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.

The .pbix file is available for download here.

Thanks,
Sam Lester (MSFT)

Comments

• Anonymous
  May 11, 2017
  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).
• Anonymous
  January 10, 2018
  Excellent Solution. Thanks!
• Anonymous
  January 27, 2018
  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?
  • Anonymous
    February 01, 2018
    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.
• Anonymous
  February 22, 2018
  Hi there, I have been trying to sort by month in adding an additional column but numerics but it is still not working ...
  • Anonymous
    April 17, 2018
    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
• Anonymous
  June 28, 2018
  Thank you for this!greate detail, loved the screen shots
• Anonymous
  August 13, 2018
  Thank you so much indeed.
• Anonymous
  August 28, 2018
  Great solution! I was almost giving up to solve it. :-)Thank you!
• Anonymous
  September 23, 2018
  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 17What 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 18Thanks in advance.
• Anonymous
  June 01, 2019
  Sam,I tried this but I have a lot of data and some of the fields are the same so it is not allowing me to use the month sort as it sees more than one value. How do you get around descriptive fields creating a conflict?