Power BI Tips: Sort by Month Name

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?”.

So here it is…

Power BI Natural Sorting of 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 of month names. When we plot any chart or a table by default we get alphabetical sort ordering, as illustrated below:

1 (2)

Obviously, this is not what we are after…

So how do we get this?

4 (2)

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 month number:

2 (2)

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:

3 (2)

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:

4 (2)

In the same way, we can dervice a new week day number column and sort weekday names in a natural sort order as shown below:

Before

dayname

After

dayname1

 

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:

DateTable4

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)

 _  
DateTable

Depending on the amount of new columns you created, you could end up with a table like this:

DateTable3

 

Look out for our next Power BI Tip and reach out if you need any further help to bring your data to life!

By | 2017-11-17T10:04:36+00:00 April 23rd, 2016|Power BI dashboards, Power BI solutions, Power BI Tips|7 Comments

About the Author:

Johann is the founder of Data Bear and specializes in getting the right data into the right hands, in a format that is quick and easy to understand. Revolutionizing your reporting experience from a time consuming frustration into dynamic interactive visualizations is what he takes pleasure in.

7 Comments

  1. Rajiv Singh December 22, 2016 at 3:40 pm - Reply

    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?

  2. Anand February 5, 2017 at 2:04 am - Reply

    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.

    • Johann February 7, 2017 at 10:46 am - Reply

      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.

  3. Ellen April 18, 2017 at 6:56 pm - Reply

    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?

    • Johann April 18, 2017 at 10:17 pm - Reply

      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

  4. Joan Martin October 4, 2017 at 9:24 am - Reply

    great and helpful blog to everyone.. thanks a lot for sharing

Leave A Comment