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:
Before
After
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! Follow our blog page for more.
I want to sort month name in Power BI but I directly connect to cube (without loading data into Power BI).
Now how can I sort my Month Name column?
There’re no option to display just month names in a proper order, but there’re solution that can be used:
1) Create calculated column
DAX formula:
MonthYear = CONCATENATE( CONCATENATE(CONCATENATE(“01-“, ‘Calendar'[monthName]),”-“),’Calendar'[year])
in text it will look like:
http://databear.com/wp-content/uploads/2016/12/image001.png
then you need to change the data type to Date and appropriate format
http://databear.com/wp-content/uploads/2016/12/image002.jpg
After that it will be displayed this way:
http://databear.com/wp-content/uploads/2016/12/image003.png
2) You can create a column that displays Months like this:
http://databear.com/wp-content/uploads/2016/12/image004.png
for example, Dax formula for this:
Month = IF(MONTH(‘Calendar'[Date])>=10,MONTH(‘Calendar'[Date]) &” “&’Calendar'[monthName],”0″&MONTH(‘Calendar'[Date])&” “&’Calendar'[monthName])
Hi Johann,
I need to sort by month as you explained in the article I got it to work. My problem is when the year changes. That is, my dates start from 1st June 2016 and now the current data date is Feb 2017. So when the data is displayed in a bar chart it goes as Jan, June, July, Aug, Sep, Oct, Nov, Dec.
What I need is to display as 2016 month first and the 2017- meaning Jun, Jul, Aug, Sep, Oct, Nov, Dec, Jan, Feb.
How can I achieve this?
Ps. I got it working by having a conditional column and assigning a month sort order by checking the month and assigning a sort order value. e.g. Jun=1, jul=2,… Feb= 9. But I wanted to know how this can be automated.
Thanks for your help in advance.
Hi Anand,
Unfortunately, I don’t think it can be achieved using DAX, but can be done in M using parameters.
I will look towards writing a follow up blog post on how this can be achieved.
Hi Johann – I’m pulling a date/time field from SharePoint that is the result of a calculation called Week Ending. This comes into Power BI as a text field. When I use this in a slicer, I get check boxes next to each week, but the dates are sorted by text. If I transform the text to date, I get that slider bar and the earliest and latest date.
How can I list the Week Ending dates in descending order with check boxes?
Hi Ellen,
Keep the data type as date. Ensure that the slicer header is turned on. In the right-hand corner of the slicer, just under the ellipsis (3 dots), next to the eraser icon, there is a drop-down list, click on this and select ‘List’.
Regards,
Johann
great and helpful blog to everyone.. thanks a lot for sharing