cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
krajani Member
Member

Rolling 12 for each month in past year based on selected month & year

Hi everyone,

 

I have to calculate Rolling 12 numbers for each month in the past year, based on selected month & year (month & year are slicers). My model is a SQL 2016 (enterprise) tabular model that is based on star schema, with a dedicated Dates column, and Employee Metrics (measures) and other dimensions (BU, Age Band, Gender and others).

 

I do have my existing Rolling 12 measures calculated using the R12 pattern by Marco Russo, works as expected for the selected year & month and looks like following;

 

R12 Head Count Base:= IF ( [Helper Comparison for max month year] = TRUE, 0, IF ( ISBLANK ( CALCULATE ( SUM ( 'EOM Metrics'[Base Head Count] ), DATESBETWEEN ( 'Dates'[FullDate], [FirstDayOfLastYear], [LastDateOfYear] ) ) ), 0, CALCULATE ( SUM ( 'EOM Metrics'[Base Head Count]), DATESBETWEEN ( 'Dates'[FullDate], [FirstDayOfLastYear], [LastDateOfYear] ) ) ) )

 

Other calculations that are involved in above formula;

 

Helper Comparison for max month year:= ( YEAR ( [HelperStart of Month] ) * 100 ) + MONTH ( [HelperStart of Month] ) = ( ( YEAR ( [Helper_Max Period] ) * 100 ) + MONTH ( [Helper_Max Period] ) )

 

FirstDayOfLastYear:=NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE('Dates'[FullDate])))

 

LastDateOfYear:=LASTDATE('Dates'[FullDate])

 

How can I generate a calculation for past 12 months based on selected month. I have to show it as a Line chart.

 

Thanks Kaz

3 REPLIES 3
Highlighted
Community Support Team
Community Support Team

Re: Rolling 12 for each month in past year based on selected month & year

HI @krajani,

 

I think it will be help if you share some sample data with expected result.

How to Get Your Question Answered Quickly


In addition, did you mean use last 12 years same month records to create a line chart?

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
krajani Member
Member

Re: Rolling 12 for each month in past year based on selected month & year

Hi @v-shex-msft

I have prepared a sample data model and PBIX file downloadable from here. And, I need DAX for showing rolling 12 for past 12 months from the selected month. 

 

Thanks
Kaz

Community Support Team
Community Support Team

Re: Rolling 12 for each month in past year based on selected month & year

HI @krajani,

 

I'd like to suggest you create a new calendar table(not has relationship to current table) as line axis to create graph.

 

Rolling calculation should break current date period, but your measure calculation has been filtered by date slicer through relationship columns.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |