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

[Anonymous]

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

[v-shex-msft]

HI @Anonymous,

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

[Anonymous]

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

[v-shex-msft]

HI @Anonymous,

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