When you perform sorting on months name, you will see that months name are sorted alphabetically as text, strings are sorted alphabetically by default. While you expect to see months name to be sorted chronologically.
Lets see, how to sort months chronologically in Power BI.
As you can see here we have a sample dataset, lets focus on date column named Date which contains a date, and Month Name column which contains a sort name of month of Date.
Lets see below report, which display a sales and profit by year, month and segment. You will notice that month’s name on x-axis is sorted alphabetically.
For year 2013, month’s name is started with Dec following by Nov, Oct, and Sep which is not a correct sorting order for months, obviously it is sorted alphabetically but for month it should be sorted in chorological order such as Sep should come first then Oct, Nov and Dec in the last.
Sort months chronologically in Power BI
To sort month name in chronological order, you need a column that contains a month number, Lets take a look on data set, you can see here we do not have a month number column, but we have a Date column that we can use to get month number.
Lets create a custom column using DAX, that will return a month number for each dates in data set.
Go to Fields pane, then right click on dataset and select New Column.
Now write a following DAX measure.
Month Number = MONTH(financials[Date])
Once you commit the DAX, lets go to Data model and you will see a Month Number column is added in dataset.
Also make sure the data type of month number should be a whole number.
Now we will use this Month Number column to sort the Month name on chart’s x-axis in chronological order.
Lets click on Data pane, then Select Month Name column after that select Sort by Column in the Sort Pane that is inside of Column Tools tab then select Month Number from drop down.
Once you select Month Number from drop down, then navigate to Report pane.
You will see the Month name is sorted chronologically automatically.
Also Read..
Visual Zoom Sliders Feature in Power BI
Implementing a table Heat Map in Power BI
