Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be 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

Reply
Anonymous
Not applicable

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

AnthonyH28__0-1643903476416.png

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@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))

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

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
Not applicable

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.