Sequencing Months and Years in Power BI
A cleaner, more comprehensible approach to the complexity of sorting out time in visualizations.
Sequencing months within Power BI can be a bit awkward, if you need to constantly shift months to show a rolling 12 or however many, while continually keeping them in the order in which they fall across years.
Solutions of the complex variety abound. Simple answers are rare, likely because most of the folks writing them have been mired in code for entirely too long. That is not to say they are wrong, just that there appears a tendency towards over-programming.
A simple solution only requires a little bit of math to produce results perfectly adapted to ordering months and their associated years in visualizations.
It can be done with or without a calendar dataset; either way, a date field is required.
In Power Query, add a [Year] and [Month] columns, both of the numeric variety.
The next step is to simply add a Custom Column with the following, which is M language, indicating to take the date and convert it to text and concatenate the two.
= Text.Combine({Date.ToText([#”Date”], “yyyy”), “ “, Date.ToText([#”Date”], “MMM”)})
The output in the new column is “2023 Mar” for March 2023.
Lastly, create one more Custom Column with the following code (M again).
=[Year]*100+[Month]
The output in the new column is “202303” for March 2023.
Mission accomplished, almost.
Back in Power BI, sort one column by the other and all months from here into eternity are sorted correctly by year and month.
Compare that with a DAX solution captured from somewhere a few months ago, hailed in some parts of the interweb as the greatest thing since sliced bread. It works just as well, relying on the same columns, but for non-programmer types is not quite as easy to wrap your head around, much less remember it over and over again.
Seq Month =
VAR MaxMos = SUMMARIZE(ALL(‘Calendar’),’A Calendar’[Year],”MaxMonth”,MAX(‘Calendar’[Month]))
VAR MyYear = [Year]
VAR MyStart = SUMX(FILTER(MaxMos,[Year]<MyYear),[MaxMonth])
VAR firstYear = CALCULATE(FIRSTNONBLANK(‘Calendar’[Year],1),ALL(‘Calendar’))
VAR myNum = IF(MyYear=firstYear,[Month],MyStart+[Month])
RETURN myNum
There are likely limitations with the simple approach, though none have been uncovered as of this writing. Power BI can slice and dice in a hundred different ways and seems to care very little whether the code is complex or simple math.
I like simple.
