Power BI: Putting your months in order
Hugh Johnson explains how to get your months in Power BI to sort according to your financial year, as defined by Sage 50 Accounts.
A common problem in Power BI is to sort your months according to your financial year and not the calendar year or alphabetically.
I presented a partial solution to this last month, but still left you with a number in front of each of the months to get them to sort correctly - e.g. “01-Apr”, “02-May”, “03-Jun”, etc. The number corresponded to the period number of your financial year in Sage 50. This is fine, possibly even better if your audience is used to reading and thinking in terms of period numbers.
In this article I will show you how you can display your months, sorted according to your financial year and without any prefixed numbers.
To support this article, I also provide a free Power BI Desktop template that includes this solution with sample data source for you to download and use as you see fit.
The solution that I present here is very simple and straightforward. It relies on the fact that in Power BI Desktop you can define the sort order of one column as that of another column in the same table.
It requires just two columns to be added to your Dates table;
‘Dates’[Month] is a field containing the month name in text format. In my example here I have just used the 3-character abbreviation for the month (e.g. Jan, Feb, Mar etc.). You can of course use the full month name.
‘Dates’[Period] is simply the accounting period for that month. It is a number from one to twelve. So in our example here, if the Month is April then the Period is 1, if the Month is May then the Period is 2 and so on.
If you have these two columns in your ‘Dates’ table, then you are good to go. Just follow these three steps:
View your model in “Data View”
Select the column ‘Dates’[Month]
Elect to sort this column by ‘Dates’[Period]
Preparing your data model
In this example, I am using Sage 50 Accounts and the Suntico BI data feed to populate my model. This technique will work equally well if you have pulled in your Sage 50 data from ODBC files, or with other accounting data as long as you have the basic elements needed in your dataset.
Somewhere in your model you will need a definition of your accounting periods (months), from which you can populate the ‘Dates’[Period] column. I am using an ‘Accounting Periods’ table from Sage 50 with some transformations applied to give me the data in the form that I want.
‘Dates’[Month] Calculated Column
The formula is:
Month = FORMAT ( DATE ( 1, MONTH ( [Date] ), 1 ), "MMM" )
This is included in my Power BI example template file that you can get here.
‘Dates[Period] Calculated Column
Now to create my new ‘Dates’[Period] Calculated Column, I can do a simple LOOKUPVALUE call to my ‘Accounting Periods’ table. I don’t really like to use this function too much as I suspect it may not be that efficient on a large table, but I am not too concerned in this case because the table that I am scanning is the ‘Accounting Periods’ table with only 12 rows.
The formula is:
'Accounting Periods'[Accounting Period],
MONTH ( Dates[Date] )
Again, you can see this in the sample Power BI template.
‘Dates’[This Year] and ‘Dates’[Last Year]
These two Calculated Columns are two analysis periods that I have created in my ‘Dates’ table. These make it very easy for me to write my Measures [Sales this Year] and [Sales last Year]. Alternatively you can just include the full definition of “Last Year” or “This Year” in each Measure that operates over these periods. I suspect (but don’t know for sure) that there might be a very small performance hit the way that I am doing it, but with the benefit of transparency and maintainability of my Measures.
If you set up your data model correctly, then getting your months to present themselves in the correct order is simple. All you need to do is select the ‘Dates’[Month] text column that you have created in “MMM” or “MMMM” format and opt to sort this column by the ‘Dates’[Period] column (that contains a Period number 1 to 12, corresponding to your monthly accounting periods).
You are welcome to try download and use a sample Power BI Desktop template file that I have created for this post.
Sample Power BI Desktop template file that I have created for this post.
YouTube video I created to show how to do everything described in this post.
How and why to create a ‘Dates’ table in Power BI.
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,...