Power BI: Rolling 12 months and YTD
Measures like year-to-date (YTD) are great at tracking performance against an annual goal, but not so good at continuously tracking the underlying health and trends of your business. Adding a rolling 12 months view to your reporting can help to solve this problem.
Okay, before we get going, let's start with a joke: what did the rolling 12 months say to the YTD? “See you at year-end”.
Charles Goodhart, Peter Drucker or a bit of both?
As we move into April, many UK businesses move into a new financial year. Targets, budgets and measures are reset and we start all over again. Year-to-date measures go from super-relevant to meaningless.
Coming up to year-end, many companies have a spike in sales as their sales team push hard to make and break targets. What really happened? Did they really win more business, or just rob a bit of future business in exchange for a bit of desperate discounting?
It might be good to really make a push at the end of every period, especially year end. To focus on specific date-driven goals can put that “stretch” into the numbers. It is a bad thing though, if you are just gaming the numbers and taking an unnecessary hit on your margins; bringing forward sales that you would have gotten anyway the following month.
What do we do when this happens? Knowing that we robbed from the new year to feed last year’s numbers, we almost ignore months one and two and then focus on a new Q1 push. The cycle continues.
To track the business, we often focus on measures like year-to-date (YTD) and full-year that in turn drive this behaviour. If we are over-obsessed with the year-end (or quarter end), then it becomes too easy to ignore the longer-term underlying health and trends of the business.
Here we are exposing one of the problems of business performance measurement. Perhaps inspired by the observer effect in physics that simply means you cannot measure something without changing what you are measuring.
In economics, Goodhart’s law states "When a measure becomes a target, it ceases to be a good measure". In plain English, be careful what you wish for; employees who are given a quantitative target will try to optimise for that, regardless of the consequences for the overall health of the company.
There is the somewhat (though not necessarily) counter argument from Peter Drucker; “If you can’t measure it, you can’t improve it”. So where does this leave us? Maybe we need to measure something else that gives us a better overall view of the health of the company and any underlying trends?
In this context, I will take a look at the classical year-to-date (YTD) measure, its strengths and limitations; and pit this against a rolling 12-month view of your business. A kind of Drucker vs Goodhart? It is not that one is better than the other, but that perhaps both used together can give you a better understanding.
YTD is such a simple, yet odd measure
YTD is easy to understand; “What have you done so far this year?” There are subtleties like whether you mean up to the end of last period so far this year, up to and including yesterday, or even today. But these are details, while the overall concept is very straight forward.
If you have a goal of 100 for the year, and your YTD figure is 15, then this is nice and clear; you are 15% of your way to reaching your goal.
What you don’t know, without overlaying this figure with additional information, is whether a YTD number of 15 is good or bad right now. The easiest way to solve this is to make a comparison with the same period last year and also compare this year’s goal with last year’s outturn. In other words, if:
then we are probably doing ok against target.
So this is a simple, easy-to-understand way to track your performance through the year. It also helps you to see progress through the year when the monthly numbers vs last year are a bit up and down.
YTD does have a major flaw though. Every time you update your YTD calculation, it is measuring something different as its scope expands through the year:
- YTD at the end of month 1 is meaningless, or at least tells you nothing you haven’t already measured with the month 1 numbers
- The significance of YTD starts from month 2 and increases through the year to month 11
- YTD at the end of month 12 tells you nothing you haven’t already measured with the full-year numbers
The problem with YTD is not so much how to measure it, but how to use it. I would argue that it is a very useful measure towards the end of the year that gives you a sense of whether or not you are on target to meet your year-end goals.
It is pretty useless, though, during the early months. We have, in other words, a measure that is in tune with and will encourage our distorted behaviour around year-end.
Because of this inconsistency, I don’t think that it is a great way to get perspective and see your underlying trends. For this, I would consider turning to a rolling 12-month view of your business.
Rolling 12 months gives you trends and perspective
A rolling 12-month measure sums up your performance over the last 12 months. Like YTD, you can do this every month, or even every day (depending on your internal processes). Its significance is that:
- Unlike YTD, it always measures against the same duration – comparing the rolling 12 months between any two months is directly relevant
- Each measurement will always contain one year-end, so minimising any distortion as you move from month 11 to 12 (last year) to month 1 (this year) due to the Goodhart effect
At year-end it is, of course, the same thing as YTD. It is, in effect, always like asking “what would it look like if it were your year-end now?”
While it is not as goal-driven as YTD, I think that there are two great ways to use a rolling 12-month figure:
- You can show in a column chart or table your actual by month over the last 12 months – If you have the data, you can also do a comparison with the same month of the previous year
- You can plot a trend-line against actual for your rolling 12 months’ measure. This will show you two things instantly:
- What is your underlying trend?
- Are you currently above, on, or below trend?
From a practical point of view, it also means that your management report can always be laid out in the same way, always showing 12 months’ data. This is good for readability and aesthetics.
I show an example in the charts below. Let’s start at the bottom. You can see that at the end of March, total invoiced sales for the last 12 months was £4.16m. This, coincidentally, is also the value for the full year last year (since March is the year end).
The column chart shows the monthly sales over the last 12 months and also for the year previous. You can see that sales are consistently higher month-on-month and year-on-year, but it is not easy to understand what that means on an annualised basis nor what the underlying trend is.
You can see this in the top chart, where I have plotted the rolling 12-month invoiced-sales figure. You can clearly see that the annualised invoiced sales are growing steadily and that since around mid-November this has been above trend. In other words, the annualised sales are not just growing but accelerating slightly compared with earlier last year.
By tracking this on a monthly, or even daily basis, you will more quickly be able to see changing trends in your business.
Measuring rolling 12 months with Power BI
Unlike with most accounting software, measuring any aspect of your performance over a rolling 12 months is relatively easy with Power BI. Once you have the basics of your Power BI model in place, there are three main things that you will need to do:
- Filter out, the year-end transfers from your P&L statement to your balance sheet (assuming that it is P&L figures that you want to track on a rolling 12-month basis).
- Establish an easy, automated way always to display the last 12 months. The way I do this is through adding a RelativeMonth column to my Dates table (this month is 0, last month is -1 etc.) and then use filters just to display months -12 to -1 and -24 to -13 for the year before.
- Add your [Rolling12Months] measure. You can see a video of how to do this here.
Power BI has no inherent concept of your accounting periods and years. It just has transactions and dates. This means a couple of things. It does mean that you have to put in a bit of work to get it to report by your financial year. It also means though, that you can very easily report across year-end and produce a more continuous and underlying view of your business.
Measuring your business around key dates like year-end may be necessary, but can also introduce distortions to your business and make it harder to see your underlying trends, particularly in the first few months of the year.
Introducing a rolling measurement that ignores your financial year can give you a more continuous, consistent view of your business. Doing this, while difficult in most accounting software, is relatively easy in Power BI.
- YouTube video - How to create a measure for rolling 12 months invoiced sales
- Guided online Power BI learning from Microsoft
- DAX basics in Power BI Desktop
- DAX Function Reference
You might also be interested in
I am a founder director of Accounting Insights Ltd, a specialist provider of Power BI reporting solutions to accountants in practice and in industry. I help accountants to use Power BI to create intuitive, engaging reports from their accounting data. I deliver management packs,...