How to Calculate Sum of 12-Month Rolling Average Value in Matrix

[bbui11]

Hi,

I have a requirement to calculate the sum of a 12-month sales rolling average measure for a given material.

Final Result would look like this:

Date Slicer: 1/1/2022 – 12/31/2023

Material #	Sum of Sales Moving Average for each month within the scope of the date slicer.
ABC	x
CDE	x
EFG	x
HIJ	x
KLM	x

I’m able to calculate the 12-month sales rolling average value appropriately on other matrix/line charts with an appropriate Month-Year axis, but I’m having a hard time summing up the 12-month sales rolling average values in the matrix above.

The matrix above would need to interact with a date slicer and various other slicers related to material #.

So pretty much:

• Would need to calculate the 12-month sales rolling average value for each month for each given material within the time frame designated by the date slicer/ other slicers

• Sum up all the  12-month sales rolling average value for each month within the date slicer, and present it in the matrix above.

Any tips/suggestions are greatly appreciated. Thanks so much for your help!

[bbui11]

Awesome! Yes, this is the proof of concept I was looking for. Thank you so much!

[Jihwan_Kim · Accepted solution]

Hi, 

I am not sure if I understand your question correctly, but I tried to create a sample pbix file like below.

please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your datamodel.

Sales total: = 
SUM( Sales[Sales] )

monthly rolling average: = 
AVERAGEX (
    VALUES ( 'Calendar'[Year-Month sort] ),
    CALCULATE ( SUM ( Sales[Sales] ) )
)

_12months rolling average: = 
VAR _selectedmaxdateslicer =
    MAX ( 'Calendar'[Date] )
VAR _t =
    SUMMARIZE (
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] <= EOMONTH ( _selectedmaxdateslicer, 0 )
                && 'Calendar'[Date]
                    >= EOMONTH ( _selectedmaxdateslicer, -12 ) + 1
        ),
        'Calendar'[Year-Month sort]
    )
RETURN
    AVERAGEX ( _t, CALCULATE ( SUM ( Sales[Sales] ) ) )

sum of 12months rolling avg: = 
SUMX ( VALUES ( 'Calendar'[Year-Month sort] ), [_12months rolling average:] )

[bbui11]

Hi,

Quick follow up question. For the "_12 months rolling average" calculation, if I wanted to include the entire dataset in the scope of the 12 month rolling average calculation, what would be the best adjustment to the measure?

Ex. With the date slicer set to 1/1/2022 -1/31/2022, the current "_12 months rolling average" measure would be equal to the total sales in january 2022, but I would want the _12 months rolling average" calculation to consider the sales for 2/1/2021 - 1/31/2022.

Thanks in advance!