By default, Power BI sorts columns alphabetically. So when it applies to months, the default alphabetical sorting order of months in a new Date table is incorrect.
The Sort by Column feature of Power BI can sort the months by name, relying on the month number which needs to contain numbers from 1 to 12 reflecting the correct order.
When the column with the month name is sorted by month number, the report becomes much better:
Still, there is an issue if the user replaces the calendar year with the fiscal year in the previous report. Supposing the fiscal year starts in July, with July 2007 being month 1 of fiscal year 2007. It turns out that January 2007 belongs to the fiscal year 2006. By creating a column for the fiscal year in the Date table, the report evolves into:
The whole red box is in the wrong place. In fact, January to June in fiscal year 2006 should be the end of fiscal year 2006 and January in fiscal year 2007 should be after December 2007. In other words, the entire red box needs to shift down towards the bottom.
Updating the Month number column to reflect a new sorting is an option – however this would mean losing the original sorting of months which is correct for the standard calendar. Instead, the correct solution is to create a new Month column, which is an exact replica of the Month name column, and call it â€śFiscal Monthâ€ť. Then, this new column can be sorted by fiscal month number.
These are the two new calculated columns:
'Date'[Fiscal Month] = 'Date'[Month] 'Date'[Fiscal Month Number] = IF ( 'Date'[Month Number] <= 6, 'Date'[Month Number] + 6, 'Date'[Month Number] â€“ 6 )
When a report slices data by fiscal year, using Fiscal Month instead of Month produces the following report, with the months sorted from July to June.
Articles in the DAX 101 series
- Mark as Date table (Jul 20, 2020)
- Using USERELATIONSHIP in DAX (May 18, 2020)
- Sorting months in fiscal calendars (Nov 25, 2019)
- Previous year up to a certain date (Sep 12, 2019)
- Using CONCATENATEX in measures (Jun 24, 2019)
- Automatic time intelligence in Power BI (May 13, 2019)
- Creating a simple date table in DAX (May 11, 2019)
- Year-to-date filtering weekdays in DAX (Apr 29, 2019)
- Summing values for the total (Apr 15, 2019)
- Counting working days in DAX (Mar 13, 2019)
- Computing running totals in DAX (Mar 4, 2019)