Power BI technique · built-in features only
How to sort month names in calendar order in Power BI
By Nawaf Sharaf · Founder & Reliability Engineer · Updated
When to use it. Any text column with a natural order needs this: month names, weekday names, stage names, size bands, fiscal periods.
Worked example
Worked example · Pellow Bakeries · the same twelve months, three sort orders
Month Name sorted as text
Sort by column → Month Number
Sort by column → Fiscal Month Number (July = 1)
Pellow Bakeries is a fictional chain. The three column charts plot the same twelve months of revenue; only the sort column changes. Sorted as text, the axis starts Apr, Aug, Dec, Feb, and the December peak of $96K, the Christmas order book, lands third from the left where nobody looking for a year-end spike would find it.
With Month Name sorted by Month Number the year reads January to December and the seasonal build to the December peak is obvious. The third panel sorts the same names by a fiscal month number for a year that starts in July: the axis runs Jul to Jun, which is how the finance team reports, with the December peak sitting in the middle of the fiscal year.
Steps
- 1
Add the sort columns
In the Date table add a month number and, for axes spanning years, a year-month key and label.
Month Number = MONTH ( 'Date'[Date] ) Month Name = FORMAT ( 'Date'[Date], "mmm" ) Year Month Key = YEAR ( 'Date'[Date] ) * 100 + MONTH ( 'Date'[Date] ) Year Month = FORMAT ( 'Date'[Date], "mmm yyyy" ) - 2
Set Sort by column
Select Month Name in the Data pane → Column tools → Sort by column → Month Number. Do the same for Year Month → Year Month Key.
- 3
Sort the visual by the axis
In the visual's More options (…) → Sort axis, choose the month field and ascending order.
- 4
Fiscal years
For a fiscal year starting in July, add Fiscal Month Number (July = 1) and sort the month name by it in a fiscal date table.
How it works
Sort by column is a property of the column in the model, not of a visual. Once Month Name is sorted by Month Number, every visual, slicer and table that uses Month Name sorts in calendar order, and the visual's own sort setting chooses between that order ascending or descending.
The rule behind the error message is one-to-one: each value of the sorted column must map to exactly one value of the sort-by column. "Jan" maps to 1 in every year, so Month Number works. "Jan" would map to 202501, 202601 and so on with a year-month key, so that pair fails; the year-month label needs its own key.
Because the sort column becomes part of the query, DAX that removes filters from the month name with ALL may also need to remove them from the sort column; REMOVEFILTERS on both columns avoids surprises.
What Microsoft documents
Limits and behaviour from Microsoft Learn that change how you build this.
- Microsoft's steps run from Report view: select the column (Month Name) in the Data pane or on a visual, then Column tools → Sort by column → Month Number. Column tools only appears in Report view. The sort then applies to every visual in the report that uses the column.
- The error "Cannot use this column for sorting" means a month name maps to more than one value in the sort column. Microsoft's examples include month numbers stored both as 1 and 01, and a rank column with ties. Fix the duplicates in Power Query or build a unique key.
- If the sort column holds numbers stored as text ("1", "10", "11", "2"), the order is still wrong; change its type to Whole number in Power Query. A column marked as a key column ignores sort-by-column and uses the default order.
- In the Power BI service, without editing the model, Microsoft's workaround is to add Month Number to the visual's Tooltips well and choose Sort axis → Month Number from the visual's More options menu.
Variations
- Fiscal calendars: keep both Month Number and Fiscal Month Number in the date table and sort a second month-name column by the fiscal number, so calendar and fiscal reports can coexist.
- Weekday names, survey answers (Strongly disagree → Strongly agree) and pipeline stages all use the same pattern.
Check that it works
After setting the sort, open a slicer, a table and a chart that use the month name and confirm all three run January to December. Then refresh the model; if the sort resets, the sort column was removed or renamed in the source.
Common mistakes
- Sort by column fails if one month name maps to several numbers (for example 'Jan' across years with a YearMonth number); use the label that matches the key one-to-one.
- Changing the sort column of a field used in DAX with ALL() can change results; remove filters on both columns.
- Weekday names need a weekday number with your week start (Monday = 1 or Sunday = 1).
- Power Query's month names depend on the locale of the data source settings; set the culture explicitly if the report is used in several languages.
FAQ
Why does Sort by column give an error?
Each value of the sorted column must map to exactly one value of the sort column. 'Jan' with a Year Month Key (202501, 202601…) breaks that; use Month Number.
Can I sort a stage column the same way?
Yes. Add a stage-order column in the stage table and sort the stage name by it.
Sources
- Microsoft Learn: Sort one column by another column
- DAX reference: MONTH
- DAX reference: FORMAT
- DAX reference: REMOVEFILTERS
Menu names follow Power BI Desktop as documented on Microsoft Learn; they can move between releases. The companies and figures in the worked example are invented.
Related
Assets and guides that go with this technique.