Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Sort Months ascending starting with current month

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 20...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago

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.