Welcome to our first of the Power BI Tips series.
Over the past two weeks, I have been asked the same question by people who have just started the awesome journey towards revolutionizing their reporting experience through Power BI:
“Instead of the default alphabetical sorting, how do I sort the months in my charts by ‘natural’ month order?”.
In other words, how do we sort by Month Name in chronological order (1 to 12) instead of alphabetically?
So here it is…
Power BI Natural Sorting by Month Name
As you may already know, it’s super easy to build a chart in Power BI, but it is not so intuitive when it comes to natural sorting by month name. When we plot any chart or a table by default we get alphabetical instead of chronological sort ordering, as illustrated below:
Obviously, this is not what we are after, we want to sort by Month Name, starting at 1 and ending at 12…
So how do we get this?
To achieve this, we need a column that contains month numbers, e.g. 1 – 12. Then, you simply sort the ‘MonthName’ column by the ‘MonthNumber’ column, and this will resolve our problem.
Let’s see how this is done:
Creating a new column in Power BI desktop that contains the month number:
Steps as per above illustrations: Fire up Power BI Desktop > edit query > Select your date table > select the date column > Add Column tab > Date button > Month > Month (again) > Select the Home tab > Closed and Apply.
Sorting a month name column by a month number column:
Steps: PowerBI Desktop > Data view > Date table > select the MonthName column> Modeling tab > sort by column button > select month number column
Let’s see the results again:
In the same way, we can devise a new weekday number column and sort weekday names in a natural sort order as shown below:
What if I don’t already have a date table in my model?
There are many ways to build a date table in Power BI Desktop, I will not cover all these in this post. My preference, mostly because it is quicker, is to create it in Power Query. However, for the purpose of this illustration, I will do it in a way most people are familiar with, using DAX (Data Analysis Expressions). This is how:
Using the ‘CALENDARAUTO’ function, it will automatically create the table with a field ‘Date’ based on the Min and Max values in your existing dataset. There are some other awesome table creating functions, more about these in another post… Next, you need to build the other required date fields. For each required field, use the ‘New Column’ button, this is found next to the ‘New Table’ button we just used. Then use the DAX formulas as illustrated below: (notice the fields to sort by)
Depending on the number of new columns you created, you could end up with a table like this:
Look out for our next Power BI Tip and reach out if you need any further help to bring your data to life!