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.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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'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.