Rolling Avg calculation should ignore date slicer

[MrMarshall]

I have a rolling average for sales data of the last 12 months and a Date slicer. The rolling Avg DAX is from Quick measures "Rolling Average".

The problem is that when using the slicer (Lets say I enter 2016), the Rolling average is ignoring the dates from the previous year (2015).

I want it to display the months of 2016, but the rolling avgerage should still be calculated using data from 12 months back.

Added a PBIX File to download with my example below.

Code with Rolling Avg Today:

Sales rolling average =

IF(

    ISFILTERED('Calendar'[Date]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    VAR __LAST_DATE = ENDOFMONTH('Calendar'[Date].[Date])

    VAR __DATE_PERIOD =

        DATESBETWEEN(

            'Calendar'[Date].[Date],

            STARTOFMONTH(DATEADD(__LAST_DATE, -11 , MONTH)),

            __LAST_DATE

        )

    RETURN

IF(FORMAT(__LAST_DATE, "yyyy-MM") IN ALLSELECTED('Calendar'[Year-Month]),

        AVERAGEX(

            CALCULATETABLE(

                SUMMARIZE(

                    VALUES('Calendar'),

                    'Calendar'[Date].[Year],

                    'Calendar'[Date].[QuarterNo],

                    'Calendar'[Date].[Quarter],

                    'Calendar'[Date].[MonthNo],

                    'Calendar'[Date].[Month]

                ),

                __DATE_PERIOD

            ),

            CALCULATE(SUM('Salestable'[Sales]), ALL('Calendar'[Date].[Day]))

        )

))

[TomMartens · Accepted solution]

Hey,

based on the pbix you provided, I added a calculated column to your calendar table, this column contains an index value for each month across the complete calendar:

month running index = 
var DateFirst = MIN('Calendar'[Date])
var YearFirst = YEAR(DateFirst)
return
(YEAR('Calendar'[Date])-YearFirst)*12 + MONTH('Calendar'[Date])

Using this kind of index columns allows to easily navigate through all kind of tables, and personally I think it makes filtering much more readable.

After this I created a measure like so:

_Sales rolling average = 
var theLastMonthIndex = CALCULATE(MAX('Calendar'[month running index]))
var theLastTwelveMonths = 
    SUMMARIZE(
        SELECTCOLUMNS(    
            FILTER(
                ALL('Calendar')
                ,'Calendar'[month running index] >= theLastMonthIndex - 12 && 'Calendar'[month running index] <=  theLastMonthIndex - 1
            )
            ,"MonthIndex"
            ,'Calendar'[month running index]
        )
        ,[MonthIndex]
    )
return
CALCULATE(
    AVERAGEX(
        theLastTwelveMonths
        ,CALCULATE(SUM('Salestable'[Sales]))
    )
    ,ALL('Calendar')
    ,theLastTwelveMonths
)

Using this measure I can create a report that looks like this:

I'm not sure, if you are familiar with this site: https://www.daxpatterns.com/time-patterns/
If not I think it's a great read.

Hopefully this provides what you are looking for.

Regards,

Tom

[MrMarshall]

@TomMartens , your solution is really clever and works like a charm. 

You just saved my day, my week, my year and you are from today my number one hero!

If you have any blog or similar, do let know and I will follow you!