Director Accounting Insights Ltd.
Share this content

The ABC of Power BI: D is for Date

What makes Power BI really stand out for accountants is the flexible way in lets you work with dates to align reports with the natural cycles of your business.

12th Aug 2020
Director Accounting Insights Ltd.
Share this content
Use Microsoft Power BI to synch reports to your business cycles

Unlike traditional financial reports that are tied to fixed periods, quarters and fiscal years, Power BI reports can analyse data over any span of fixed or relative dates. This is really useful if your business cycles don’t match conventional reporting periods.

The Power BI chart I created for a client (below) shows daily sales over a rolling 13-week period. The actual numbers are greyed out for client confidentiality. The colour coding shows how the sales on any particular day compare with the daily average over the 13-week period. Green is above average, red is below and white is average.

Rolling 13-week period sales chart built with Power BI

Without looking at the numbers, you can still see the main points straight away:

  • Thursday, Friday and Saturday account for most sales (especially Friday)
  • Sales are lowest from Sunday to Wednesday (especially on Sunday and Monday)
  • Total sales in each week are similar.

Now, for argument’s sake, imagine that 80% of your sales are from Thursday to Saturday and 20% from Sunday to Wednesday. In this situation, your total monthly sales will depend on the mix of days of the week in the given month. To simulate this in Power BI, I created a simple budget model where on each day between Sunday and Wednesday, the daily sales budget is £500 and on Thursday to Saturday it is £2,667. This provided the 80/20 split, with a sales budget of £10,000 per week.

Plotting budgeted sales by month for 2020 gave me the following chart:

Power BI: Budgeted sales by month report for 2020

October only has one day more than September, but sales are 17% higher because it has five Thursdays, Fridays and Saturdays versus only four in September.

The magnifying effect

The effect described above is magnified in the bottom line, particularly if your overheads and gross margins are high.

When I put overheads into the model at a flat rate of £20,000 per month, and assumed a 50% gross margin, the P&L budget came out like this:

Monthly P&L adjusted for overheads

So, although sales are constant at £10,000 per week, the gross margin is a constant 50% and the overheads are a constant £20,000 per month, there are these wild swings in net profit from one month to the next. The misalignment of the daily and weekly sales patterns with the calendar months causes this.

The monthly P&L figures are correct, you can still use Power BI to generate them, as in this example P&L report. The problem is how to interpret swings in your P&L when you can’t separate cycle misalignment from real changes or anomalies in your business patterns. One method is to track your P&L say on a rolling 12-month basis. This technique will even things out a lot, but recent spikes or troughs may only have a small impact and be harder to detect.

Power BI lets you do create rolling P&Ls quite easily. You can have a rolling x-month P&L that increments on a daily basis, not just at month end, perhaps delayed by n-days to allow for postings and adjustments to catch up. This is great, but will only take you so far.

You may want to supplement your traditional monthly reports and rolling monthly analyses with more granular periods that sync with the your business cycles. In the example above, it could be appropriate to track overheads on a rolling calendar month and gross margin elements on a rolling 7-day (or multiple of 7) basis. If you set up this alignment in your reports, then you will be able to spot, understand and interpret trends and outliers much faster.

You could even create a rolling “underlying monthly P&L” that stretches the gross margin elements to realign with your overheads. This would eliminate the distortions caused by the mix of days in the month and, in this case, give you a much clearer picture of your underlying trends and changes.

How Power BI handles dates

When you work with accounting data in Power BI, the most natural unit of measure for time is a single day. A single day can have any number of attributes, such as date, day of the week, age, week, period, fiscal year, calendar year and working day. You can aggregate days to analyse by date, day of the week, working day, week, aged weeks, rolling x days.

You can add regression over a series of dates or periods to spot trends, or a second-order regression over pairs of periods to take into account seasonality and the underlying acceleration of any trends. Additionally, since you know today’s date, then you can shift to a more relative mode of reporting, such as “this month”, “last month”, “last week”, “last FY”, “last 28 days”, “rolling calendar month” and so on.

Measuring your business on a daily basis can also be very useful to monitor things like liquidity. Take a look at the example below:

Daily working capital balance report

If you just track your balance sheet on a monthly basis, the example above will show a decrease in working capital over the month of £6.5k. What this disguises, though, is the £100,000+ difference between the peak on 5 Jan and the trough on 19 Jan, which may need additional funding.

It’s all relative

Let’s say that you are responsible for collections in a small to medium sized business. You probably review and plan your activities and report to the finance committee on a weekly basis. Daily is too fine-grained. Monthly is too coarse and slow-moving. In this context, standard aged debtor reports are not very useful.

For the collections manager, a timeline of due dates by week can feed into the weekly statement, email and call plan:

Weekly due dates report generated by Power BI

For the weekly finance committee, a simple gauge showing how we are progressing vs expectations for the month is very easy to digest:

Power BI gauge showing expected monthly receipts

In this case, in the background Power BI has analysed all of the receivables, examined the average time it takes each customer to pay and come up with an estimated cash value of £197k for the month.

Get Power BI to work the way you need

Key to making all of this work, is to set up your Dates table (or tables) in Power BI.

There are broadly two technical ways to do this; one is to use DAX as I demonstrated in my Power BI basics article, and the other is to use Power Query as I do in this YouTube tutorial.  There are advantages and disadvantages for either approach.

DAX has the following advantages:

  • Its concept of “Today” is the day that you opened the report that you are looking at (most likely today). In Power Query, by contrast, “Today” will be the last day that you refreshed the data (the Dates table) in your model. This may or may not be today.
  • Its code is more transparent to anyone picking up your report (that is, you don’t need to open Power Query and examine the steps and possibly the M code used to create the Dates table).
  • If you know how to write DAX but not Power Query M, then you will be more at home creating a DAX Dates table.

Power Query has the following advantages:

  • If you add columns to your Dates table that have the type “Text”, such as “May-20”, “Jun-20” and so on, a Power Query Dates table will take up (potentially quite a lot) less space in your model and perform better. This is because Power BI does an extraordinarily good job at compressing data that it loads through Power Query.
  • Even if you don’t know how to write any M code, there are a lot of very advanced date transformations that you can do in Power Query without writing any code. I show some examples here.
  • If you work on many Power BI reports with a standardised Dates table, it is easy to cut and paste an entire Dates table from one report to another in Power Query.

Summary

One of the most difficult and valuable things to do in financial reports is to surface underlying trends and changes in time to take useful action. As we have seen in this article, the patterns of your business can be obscured, rather than revealed, by fixed monthly reporting periods.

Power BI gives you the opportunity to transform this, by adding on whatever date-driven overlays are relevant to your business and its data. With Power BI, it is not a case of “either/or”, rather “yes/ and”.

Yes, you can use Power BI to continue to produce your more traditional reports, but with perhaps greater automation and flexibility to slice, dice and drill down. You can supplement this, though, with different analyses that are more in line with your natural business cycles to uncover better insights faster.

Useful links

An example Profit & Loss report in Power BI

How and why to set up a Dates table in Power BI – a beginners guide

How to create a simple, dynamic Power BI dates table using DAX

How to create a simple, dynamic Power BI dates table using Power Query

How to copy a query (for example a Dates table) from one Power BI report to another

Examples of simple date transformations in Power Query

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.