A common problem when working with accounting data in power bi is to get
your months to sort correctly according to your financial year instead of the
calendar year. In this example I'm working with a company data set whose
financial year runs from the 1st of April through to the 31st of March so
the order I want my months to be presented in would be from April through to March.
Of course by default power bi will display your months starting from January and
going through to December. Let's have a look at how we can change that to start
from April going through to March. In my dataset we're going to look at at a
couple of tables I've got one table that I've pulled in from Sage 50 which
contains my accounting periods as defined in Sage 50. Let's take a quick
look at that data set. We have the accounting period one through to twelve
We've got the period description so we can see my fiscal or my financial year is
running from the 1st of April 2017 through to the 31st of March 2018 for
this current financial year. Separately I've extracted the start of the period;
the end of the period; the number of days in the period and the month number. So
April is month number four for example. What we have now in my table is
accounting period 1 corresponds to month number 4 and that's the key to resolving
this problem. How I extracted the startup period end of period days in period from
this period description you can see in a separate video. So that's my accounting
period table and I have a dates table take a look at that I have my date and
this is just a set of continuous dates incrementing one day at a time across my
entire dataset. So I have my date and I've got a calculated column called
period which references my accounting periods table and what I'm using is
lookup function and taking the month of the date I'm referencing the month in
the accounting periods tables or the month number and using that to look up
the corresponding accounting period and writing that into my dates table. So
in my dates table and have the date and the period for every single date in my
table. The next field I've created in my dates table is a simple text field
displaying the months using three characters. I'll just add that in then
I'll show you how I've done that and I've used a very simple trick using the
format function within DAX and I've created a dummy date of the first day of
the first year and then I've taken the month of the date in the dates table
and then I have asked the format function to format that as mmm format which just
gives me my Nov Dec Jan and so on. So that gives me my months that I
want to display on my reports that matches the the corresponding date and
if Igo back to the report, if I want to create say for example sales by month
this year and last year I go to maybe select a visualization for a bar chart
and I go to my measures and I have some measures I prepared earlier so I
have sales this year I have sales last year and I want to show that by month to
go to my dates table and here straight away we can see the problem it's
presented because the month column is now a text format column it has just
displayed that in ascending alphabetical order. Now I want to reorder that
according to my financial year. So the field that contains or the column within
my dates table that contains the order the sort order that I want is my period
information. So to fix that is actually a very simple trick within power bi. If I
go to my data view and select the month column and then go to this sort
by button and by default is sorting by itself the month
but instead I can choose it to sort by the period. Now if I go back to my report
view what you see now is the data is correctly sorted starting in April and
going through to March. It doesn't just work for this visualization that's
now saved into the report it also works for other visualizations as well so for
example if I wanted to add a slicer by month, pop in the month there and you
can see the slicer is presenting the months in the correct order. So that's a
very simple trick that you can use within power bi to get your months to
sort according to your financial year instead of the calendar year