Sorting months according your financial year in Power BI - YouTube

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