Moving Average of Multiple Series

[cld32650]

I would like to calculate the moving average of multiple series data as a measure. The data is monthly spanning 40+ years. 

The columns of data are "Scenario", "Year", "Series", and "Value". A slicer filters the table for "Scenario", but no filtering is performed on series because I want to visualize the moving average of each series in one figure. Table below shows how data is currently formatted (Aggregated to annual for simplicity).

Scenario	Year	Series	Value
1	2020	1	100
1	2021	1	106
1	2022	1	109
1	2020	2	98
1	2021	2	99
1	2022	2	105

Ignoring the edge cases in this scenario, the 3 year moving average in 2021 for series 1 would return 105; the 3yr moving average in 2021 for series 2 would return 100.67.

The data will be displayed on a scatter plot as a tooltip, where the scatter plot displays other data for every year, and series, filtered by a scenario slicer. 

To get started I have tried to move the calculation to a calculated column, but the calculation is simply returning the same number from the Value column on the same row (almost like a copy/paste to another column).

Below is the code I have tried. Any suggestions to get the calculation correct would be greatly appreciated - keep in mind please the end goal is to move all of this to a measure.

Var PeriodToUse = 
    Filter(
        All(testtable[Year]),
        And(
            testtable[Year] < 2030,
            testtable[Year] > 2026
        )
    )
Var Result =
    Calculate(
        AVERAGEX('testtable',[Value]),
        PeriodToUse
    )

RETURN
Result

[suparnababu8 · Accepted solution]

Hello @cld32650 

I hope you are looking for this. Attched pbix file here for your reference.

Thanks!

[cld32650]

Thank you for the worked example. For others that are interested, the code within the file is copied below -  note, this creates a trailing moving average, whereas the question is about a centered moving average. An additional variable to define the end year was the only needed adjustment to finalize the code.

Moving Average 3 Years = 
VAR CurrentYear = MAX('MovingAvgExample'[Year])
VAR StartYear = CurrentYear - 2
RETURN
CALCULATE(AVERAGEX(
        FILTER(ALL('MovingAvgExample'),
            'MovingAvgExample'[Year] >= StartYear && 'MovingAvgExample'[Year] <= CurrentYear &&
            'MovingAvgExample'[Series] = MAX('MovingAvgExample'[Series]) && 'MovingAvgExample'[Scenario] = MAX('MovingAvgExample'[Scenario])),'MovingAvgExample'[Value]  ))

[v-linyulu-msft]

Hi,@cld32650 
We are delighted that you have found a solution and are willing to share it.

Accepting your post as the solution is incredibly helpful to our community, as it enables members with similar issues to find answers more quickly.

Thank you for your valuable contribution to the community, and we wish you all the best in your work.

Best Regards,

Leroy Lu

[ryan_mayu]

you can try this

Column = CALCULATE(average('Table'[Value]),ALLEXCEPT('Table','Table'[Scenario],'Table'[Series]))

or 

Measure = CALCULATE(average('Table'[Value]),ALLEXCEPT('Table','Table'[Scenario],'Table'[Series]))

[cld32650]

Thank you for the response. I have not verified this solution because another workable response was provided earlier.