Calculate Rolling Average with Consideration to Data Preceding Date Slicer Selection

[bbzzbb809]

Hi,

I am looking to calculate the 12 month rolling average value for some sales/material data that I have. Currently, my 12 month rolling average calculation is working, but it only takes into account sales data based on the respective date slicer range.

I'm hoping to update the calculation to include sales data that precedes the range defined in the date slicer.

When >12 months of data is selected the measure works as intended, since there are 12 months of data in the range of the date slicer.

Screenshot below shows a date slicer selection of 14 months, and therefore the "_12months rolling average: based on max date of range" measure differs from the "monthly rolling average:" measure (This just takes the average of the entire date range), as expected.

The issue occurs when <12 months of data is selected in the date slicer. 

Screenshot below shows both measure outputs matching, but my entire sales dataset starts in 1/1/2022, and therefore I would want the true 12 month rolling average value for September 2022, rather than the "rolling average" of the date range defined by the slicer.

Below are my measures

monthly rolling average: =

AVERAGEX (

    VALUES ( 'Calendar'[Year-Month sort] ),

    CALCULATE ( SUM ( Sales[Sales] ) )

)

_12months rolling average: based on max date of range =

VAR _selectedmaxdateslicer =

    MAX ( 'Calendar'[Date] )

VAR _t =

    SUMMARIZE (

        FILTER ( // Filtering down calendar table based on date range selection

            ALL ( 'Calendar' ),

            'Calendar'[Date] <= EOMONTH ( _selectedmaxdateslicer, 0 )

                && 'Calendar'[Date]

                    >= EOMONTH ( _selectedmaxdateslicer, -12 ) + 1 //The # of months includes current month, so -11 would be a full year

        ),

        'Calendar'[Year-Month sort]

    )

RETURN

    AVERAGEX ( _t, CALCULATE ( SUM ( Sales[Sales] ) ) ) // Returns the averge of sales for the last 12 months based on the filter context


Here is a link to the PBIX file:https://drive.google.com/file/d/1X9Q862JSz5vTZGDAOwchjZo81C-xPcFD/view?usp=sharing

The end requirement is to retrieve a sum of the "12 month rolling average values per month".

Please let me know if any additional information is needed. Thanks in advance!

[bbzzbb809]

Hi Amit,

Thank you for the response. I just tried using this measure, but it is still running into the same issue as my "_12months rolling average: based on max date of range" in that it doesn't take into account the data preceding the date slicer selection for the rolling average calculation.

[amitchandak]

@bbzzbb809 , try a measure like

12 Month Avg = CALCULATE(AverageX(Values('Date'[MONTH Year]),calculate(Sum('Table'[Value])))
,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))