In Power BI, the default sort order of text is alphabetical. But of course, this doesn’t make sense for months as they should be sorted from January to December.
In the following article, you will learn something that you will use in almost all your Power BI reports. Why? Because you will most likely have months in a visual that should not be sorted alphabetically.
This is the default sort order of months in Power BI:
Whenever you want to sort text by a different logic than alphabetically, you need to have a numeric column in your data model, which indicates the sort order. You can then tell Power BI to sort the text by this number. In the following article, we’ll look at some specific cases.
In order to be able to sort from Jan – Dec, you need a column showing the number of each month ( 1 – 12 ).
With a date table like the one above, you can show the Month Name in the visual but still sort correctly by the Month Number column. So how is the sort order set up? First, let’s add a Zebra BI Chart visual and show Cost per Month Name. To do this, add the Month Name from the Calendar table to the Category placeholder and the Cost field from the Sales table to the Values placeholder.
The default sort order is by values, so you can see the highest value on the left and the lowest on the right.
To change the sort order to Month Name, select the visual and click More Options (…) > Sort by > Month Name.
The months are now sorted alphabetically descending from Z – A
Let’s change the sort order to ascending A – Z by selecting the Visual and clicking More Options > Sort ascending.
The months are now sorted alphabetically from A – Z
Now follows the most important step. We need to make sure that the months are sorted by the Month Number and not alphabetically. To do this, select the Month Name from the Fields and then Sort by Column > Month Number.
The months should now be sorted in the correct order, from Jan to Dec.
Sorting months from January – December is one of the basic sorting skills but there’s more to learn. What do you do when you have a hierarchy that should be sorted in a specific way? Learn how this can be done.
Or what do you do when you want to add a custom sort logic but the column with the numeric value is missing? We also have an explanation for that, check it out.