Finance “Close” Calendar for Power BI
In a previous post, I showed how to create a Date table in less than 5-minutes using Power Query. For most uses, that 5-Minute date table is perfectly sufficient. Recently, however, I was asked by a client to develop a date table that would align to the finance close cycle. What that means requires a bit of explanation for non-finance readers.
The Close Cycle
At the start of a month, companies work through a “close” process, which means finalizing the revenue and expenses for the previous month and preparing the various financial reports for consumption by business analysts, company executives and external stakeholders. Typical close processes last between 7 and 14 days, meaning it can take up to 2 weeks for numbers to finalize. During that period, financial reports are not accurate. Typically, during the close weeks, Actual numbers are lower than Budget numbers, which leads to negative variances. It’s not uncommon for company executives to see these premature results, become alarmed and demand explanations about why the numbers looks so bad this month. The reason why premature results become visible is that, at the beginning of the month, many reports automatically rollover and begin displaying data, well before the close is finalized. My client asked me to develop a Power BI calendar that would prevent the display of results until numbers were finalized. Essentially, I needed to build in a 2 week delay to the calendar, so that the month would roll over only after the close cycle had closed. Although it took a bit of experimentation, I created a date table that had some pretty cool features:
Update on a pre-specified day, that could easily be changed using a parameter to match fluctuations in the close cycle
Automatically change which months were categorized as YTD, Rolling 3-Months and the Current Month
Identify Current Year, Prior Year and Prior 2
Although this calendar is more complex to build than 5-Minute Power Query calendar, it still requires very little M coding (M is the programming language for Power Query). Building the Close Calendar does require the use of a parameter which is used to create the delay between the close date and the actual date. Apart from that new element, most of the work is easily understood and executed.
Getting Started
As with the simple calendar, open a new “Blank Query” data source and type =List.Dates. Click on the check mark which saves the code and brings up a Parameter input tool.
Enter a start date, the count of days for the calendar, and the step (1 for 1 day).
Convert to a table
They key to the Finance calendar is adding a Current Date column and a Current Date Adjusted column. The difference between the Current Date and Current Date Adjusted should align with the close process. If it takes 15 days to close the books, then the difference between Current and Adjusted should be -15 days -30 days, for a total of -45 days. This need for an additional 30 day delay may at first be counterintuitive, but should make sense with a bit of explanation.
If the current date is June 30th, financial reports will be showing May results. As the month rolls over to July, the close period begins and the June close/reconciliation starts. Until the books are closed, on July 15, it’s important to continue showing May results rather than June’s. So, the -30 days is used to trick the fiscal calendar into behaving as if the latest reported month is May. On July 16, when the books are closed and reconciliation is complete, then the calendar will show the current adjusted date as June 1st and will flip the current reporting month from May to June.
The first columns I add to the table are
DaysFromCurrentDate
CurrentDate
CurrentDateAdjusted
Comments