Setting analysis periods in Power BI for your Sage 50 data
Traditional accounting reporting is monthly, quarterly, annual and maybe a 30/60/90+ view for receivables. You can replicate this in Power BI, but if this is all you do you are missing a trick.
Power BI offers you new ways to see your Sage 50 data simply by creating new, dynamic analysis periods. By taking advantage of this you can turn your Sage 50 Accounts data into something that is much more alive, delivering more immediate and actionable insights. I have spent a lot of time figuring this out and I believe that I have come up with a set of analysis periods that you may find really useful.
In this article I share with you my thought processes and the current model that I am using. Hopefully this will save you time getting started with Power BI Desktop to analyse your Sage 50 data.
What is an analysis period?
An analysis period is a time slot over which you want to summarise your Sage 50 data. In Sage 50 these periods are simply months and years. But they don’t have to be just that. They can be a single day, like yesterday, or the last 7 days. They could be more obscure like “Every Saturday so far this year”. So what I mean by an analysis period is a defined set of dates over which you wish to summarise your Sage 50 data.
Limitations of standard monthly reporting
A company that relies just on standard monthly reporting is always looking into the past. The most recent management report may be nearly a month old. It may be a week old before it is even produced and shared. It usually carries a fixed, flat format that is optimised for printing and may deliver very little insight into what needs to be done now or next week or next month.
The power of Power BI
Once you have solved the problem of getting your Sage 50 data refreshed in Power BI every day you can start to get really creative around your analysis periods. Quite simply, if you can define an analysis period in a formula, then you can put this into Power BI. You can create rolling time periods and calculate trends off these that update every day.
Multiples of 7 and period-end effects
A classic problem with traditional monthly analysis is that no two months are equal. If you look at the UK for example, and if you assume that public holidays and weekends are not working days, then in March this year there were 23 working days versus only 18 in April. That is an extra full week’s work in March!
Even if your business is open every day because of a significant online presence, your trading patterns will be different on different days of the week and on public holidays. Your trading patterns from month to month will be affected by the mix of weekdays, weekends and public holidays that make up the month. If you want to see the underlying trends of your business more clearly, then you need to try to remove this effect.
Working Days in the UK in 2017
A partial and simple solution to this is to create analysis periods that are in multiples of 7 days. It does not solve every problem, but at least you will always be looking at the same number of weekdays vs weekend days in your analysis. So the “Last 28 Days” will always have four of each day of the week. It is also a fairly close approximation to a month, with other approximations being a quarter is 91 days and a year is 364 days.
A 28-day / 91-day / 364-day analysis cycle still presents some problems though because many businesses have peaks and troughs around month end, quarter end and year end. The “Last 28 days” will usually but not always incorporate one month end. The “Last 35 days” would solve this problem but for someone trying to understand a quick rolling number I think that a mental image of 28 days is just easier to process.
A hybrid 7-day/monthly model
The approach that I have taken is to adopt a hybrid model that uses 7-day multiples for rolling period and trend analysis as well as the more traditional monthly/yearly model.
I describe some of the analysis periods that I use and their purpose here.
You will see that I have added these fields as Calculated Columns rather than Measures.
This is because these are basic building-block attributes that I want to assign to every single date in my Dates table.
They take two forms; True/False and date Categories.
I use True/False definitions where I am just wanting to use the analysis period as a true/false filter.
For example, the DAX expression for Last 28 Days is simply:
Last 28 Days = IF([Date]<(TODAY()-28),FALSE(),IF([Date]<TODAY(),TRUE(),FALSE()))
I define date Categories where I want to add a Slicer to a report or if I want to use this field say as an axis within a visualisation. An example of this is Aged Period, which we will look at next.
For receivables analysis
My Aged Periods reflect what I already have in Sage for aged debtor analysis. Every date in my Dates table is categorised into an aged period that I can add to a slicer or a bar chart.
This is nice because you can instantly see a visual snapshot in the standard aged debtors categories that you are already very familiar with. Unlike a standard Sage 50 report you can easily drill down to analyse aged debtors by customer, department, sales rep, product or product category. It is not very actionable though. The problem is the very coarse nature of the analysis periods. The first 8 to 12 weeks really need to be looked at more closely.
To give a finer-grained look at my receivables I have defined Aged Weeks to look more closely at outstanding invoices particularly in the first eight weeks. I assign every date in the Dates table an Aged Weeks value that reflects the number of completed weeks old, going back from today.
This gives a much clearer insight into what I might expect in terms of cash coming into the business in the coming weeks and what resources I might want to direct towards collections.
I can see that I have a peak of receivables that are two weeks old.
I can drill down to see this in more detail, but already this tells me much more than the current 0-29 Days category they belong to.
I can see from a cash-flow point of view that unless anything is unusual (like a single very large debt), most of this will probably be collected by about week six and that I should start a focused effort around this next week.
For traditional but much more powerful monthly reporting
Is This Year or Last Year
These are analysis periods defined by your fiscal year and are simple True/False fields to support filters. As I work with multiple clients, I have set my model to read the periods information from Sage 50 and set them in Power BI automatically. If you are only working with one company’s data then you can simply hard-code this into your model. This Year and Last Year enable you to do year-on-year comparisons by month in the same chart.
I have had to create the analysis period Month so that I can put the month number into the field text so that Power BI will sort it correctly.
The visual below is for a company whose fiscal year runs from 1st November to 31st October.
I have also included an analysis period called Next Year to view forward-dated transactions or forecasts.
The period Last Month is as it says. It is useful to have this as a filter for my data if I want to just look at last month’s numbers.
Is last Year or this Year is simply there to use as a page-level filter if you only want to display data from last year or this year. Be careful though, because if in the background you are looking back over older data to calculate trend Measures then applying this filter will affect those calculations by removing the older data from the calculations.
For rolling analysis
Last Invoice Day
Last 7 Days
Last 28 Days
Last 91 Days
This Year to Yesterday
Last Year to Yesterday
In my opinion Power BI really comes into its own when analysing Sage 50 data over rolling periods. Since Power BI understands “Today”, it is possible to do all kinds of analysis relative to today, therefore giving an updated picture every day.
The rolling periods I have defined above are self-explanatory and simple True/False fields. “This Year to Yesterday” and “Last Year to Yesterday” are for a year-to-date comparison with the same period last year. Using this I can create a Measure to show Sales Growth YTD which is automatically updated every day.
Next Month, This Year, Next Year
If you plan to use Power BI to calculate trends and forecasts as I do, then these three analysis periods will also be useful to you. These are also simple True/False calculated columns.
Analysis periods for slicers
A slicer is a very user-friendly and interactive way to apply filters to your Power BI report from within the report canvas. To support a slicer you need your rows to be classified in some way. The simple True/False classification for most of my analysis periods will not work for this. The three that I prepared are Rolling Period, Month and Year.
Date “Measures” are not analysis periods, but are useful single-value dates that you may want to use throughout your Power BI report. The Measures that I use are simple dates or counts of days.:
First day of last fiscal year
Last day of last fiscal year
First day of this fiscal year
Last day of this fiscal year
First day of next fiscal year
Last day of next fiscal year
Last invoice date
Counts of days
Days this month
Days remaining this month
Days next month
Days this year
Days remaining this year
Summary and to find out more
Setting up predefined analysis periods in your Dates table in Power BI is essential if you want to do serious date-driven analysis of your Sage 50 data. There are different approaches to take and I have shared in this article the approach that I am taking which is a hybrid of traditional monthly reporting as well as rolling period analysis.
What I have not shown you here is how you would build on this work to create the summaries and visualisations that you want. This will be the subject of a future article.
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,...