Sorting by Month Names in Power BI


How do I sort by month number instead of alphabetically by month name in Power BI?

As I continue to work with customers to develop Power BI dashboards, this topic of sorting month columns comes up quite often. There are a few great threads and blog entries with instructions, but the following screenshots and steps seem to be working well for my customers, so I thought it might make for a nice blog entry.

This “Sort by Column” concept can be applied to sorting other columns as well, but the application for month names / month number is the majority case that I’ve seen.

Steps:

  1. Add a new column that represents the intended sort order (ex: Jan = 1, Feb =2). This can be a calculated column using the MONTH() or FORMAT() function to apply to a date column or manually created. You can also use this if your organization uses a different fiscal year than the standard calendar year.
  2. On the fields pane, select the Month column, navigate to the Modeling tab, click "Sort by Column", and update the sort to select the SortNumber column created in step #1. Tip: Make sure your new column is an integer/whole number, so the sorting is numeric.
  3. Add your visuals to see the sort order reflected as intended.

Power BI Sort By Month Name

The .pbix file is available for download here.

Thanks,
Sam Lester (MSFT)

Comments (10)

  1. Leo Espejo says:

    Hi Samuel, very nice solution to override alphabetical sort order. I tried to use this recommendation in Power BI Desktop. I had to change from DirectQuery to Import to be able to use Sort Column in Modeling tab, but here comes the real problem: PBI does not let me do the sort by the “numeric” field, because is related to the Month column (as month number is obtained from month name column).

  2. Marcos Mercado says:

    Excellent Solution. Thanks!

  3. PKFIT says:

    How do you create this manually if you don’t have date column but only a column with month name and another column with year?

    1. One option would be to created a calculated column with the concatenated values. Ex: [Month_Name] & ” ” & [Year_Number] or by using the CONCATENATE() function. Then you can create the SortOrder column and continue with the steps above.

  4. Alexandra Partouche says:

    Hi there,

    I have been trying to sort by month in adding an additional column but numerics but it is still not working …

    1. Albert says:

      It worked for me. Do you still need help? Email me the screenshot of your visual and I’ll try to help. albert.jeey@gmail.com

  5. Hani says:

    Thank you for this!
    greate detail, loved the screen shots

  6. Khin Kye Sin says:

    Thank you so much indeed.

  7. Marcelo says:

    Great solution! I was almost giving up to solve it. 🙂

    Thank you!

  8. Michael says:

    Great solution, I learned something new today. Means it is a good day.

    What about if you want to look at the last 12 months, across two years? Ie Sep 2017 – Sep 2018?

    I want the odering to be by Calendar Month, not by month.

    What it’s doing now:

    Jan 18, Feb18, Mar 18, Apr 18, May 18, Jun 18, Jul 18, Aug 18, Sep 17/18 (combined), Oct 17, Nov 17, Dec 17

    What it needs to do:

    Sep 17, Oct 17, Nov 17, Dec 17, Jan 18, Feb18, Mar 18, Apr 18, May 18, Jun 18, Jul 18, Aug 18, Sep 18

    Thanks in advance.

Skip to main content