March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.
Solved! Go to 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.
@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))
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.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
170 | |
145 | |
90 | |
67 | |
58 |