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.
- 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.
- 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.
- Add your visuals to see the sort order reflected as intended.
The .pbix file is available for download here.
Sam Lester (MSFT)