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] ) )
How can I generate a calculation for past 12 months based on selected month. I have to show it as a Line chart.
I think it will be help if you share some sample data with expected result.
In addition, did you mean use last 12 years same month records to create a line chart?
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.