Sort Months ascending starting with current month

[Anonymous]

I inherited a calendar table in this model with a nice set of options. The request I received is that they want to see rolling 12 months but also compare the previous. So we would be comparing Jan 2022 back through Feb 2021 against Jan 2021 back through Feb 2020.

Using this calendar table, I have easily created the measures to get the data inside those date ranges - thats easy.
Whats tripping me up is a way to sort this by trailing 12 months when all they want to see is the month for the X Axis.

I know really what I need is a way to number from 1 to 12, with 1 being current month and do that then for each year but I cant think of an elegant way to do it in this Calendar table so that it updates every day. In 6-7 years of doing this kind of work, never had someone request the data quite like this but it does make sense for how this company runs and the cyclical nature.

Heres a snippet of the Calendar Table Im working with.

[amitchandak]

@Anonymous , Example 1

Use all(Date) in filter if needed

Try like

Rolling 12 Sales =
var _max = maxx(allselcted(date),date[date]) // o4 today()
var _min = date(year(_max), month(_max)-12,1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(date, date[date] <=_max && date[date] >=_min))

Rolling 12 before 12 Sales =
var _max1 = maxx(allselcted(date),date[date]) // o4 today()
var _max = date(year(_max1), month(_max1)-12,1)
var _min = date(year(_max), month(_max)-12,1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(date, date[date] <=_max && date[date] >=_min))

example 2

every month will be rolling 12

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

Rolling 12 before 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-12) ,-12,MONTH))

[Anonymous]

All of this looks like the measures to get the data inside those date ranges which I noted that I have, but I appreciate the other methods! Look at the image I posted - we have columns for current month offset, so its easy to just do a Calculate on the data and then filter by when CurrMonthOffset is between 1 and 12. That column is dynamic and updates everyday as needed.

What I need is a way to add a column in the Calendar field that will rank/sort the months by current, back 12 months. and then repeat that same pattern for each year. This is so in graphs I can sort the months in that order, most recent to oldest and I need to apply that sort to my Month field.

Example:
This month is February, so that month would be Month 1, January would be 2, December would be 3 on back to March which would be 12. But then February of 2021 would be 1 again and so on.

[Anonymous · Accepted solution]

Figured it out!

Month Sort =
IF (
MONTH (
CALCULATE ( MAX ( 'Calendar'[Date] ), 'Calendar'[CurrentDayOffset] = 0 )
)
< MONTH ( 'Calendar'[Date] ),
12
- ABS (
MONTH (
CALCULATE ( MAX ( 'Calendar'[Date] ), 'Calendar'[CurrentDayOffset] = 0 )
)
)
- MONTH ( 'Calendar'[Date] ) + 1,
MONTH (
CALCULATE ( MAX ( 'Calendar'[Date] ), 'Calendar'[CurrentDayOffset] = 0 )
)
- MONTH ( 'Calendar'[Date] ) + 1
)

I was able to come up with a mathematical formula and then just had to convert that into DAX.