Moving Average of Summarized table is returning single value from row context

[cld32650]

I am trying to calculate a centered moving average of multiple series data, but am having trouble getting the moving average to return something other than the value at the current year row context. Seems like the "StartYear" and "EndYear" filtering variables are not effective, but not 100% sure that's the problem. Any help would be greatly appreciated. I have created an example table at the bottom, showing the format of the virtual tables. Note, additional helper columns exist in the virtual table, but are not needed

VAR MovingAvg = 
    CALCULATE(
        AVERAGEX(
            FILTER(
                SUMMARIZE(
                    UNION(
                        SELECTCOLUMNS(SummarizedResults, "Year", [Year], "Value", [Value_group1], "SeriesIDCY", [SeriesIDCY], "Scenario", [Scenario], "Series", [Series]),
                        SELECTCOLUMNS(FinalPublishedData, "Year", [Year], "Value", [Value_group2], "SeriesIDCY", [SeriesIDCY], "Scenario", [Scenario], "Series", [Series])
                    ),
                    [Year], [Value], [SeriesIDCY], [Scenario], [Series]
                ),
                [Year] >= StartYear && [Year] <= EndYear &&
                [Series] = MAX([Sereis]) && [Scenario] = MAX([Scenario])
            ),
            [Value]
        )
    )

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.

[Anonymous · Accepted solution]

Hi, @cld32650 

Based on your information, I create a table:

Then create a new measure, try the following DAX:

MovingAvg = 
VAR CurrentSeries = MAX([Series])
VAR CurrentScenario = MAX([Scenario])
VAR CurrentYear = MAX([Year])
VAR YearRange = {CurrentYear-1, CurrentYear, CurrentYear+1}
RETURN
CALCULATE(
    AVERAGE([Value]),
    FILTER(
        ALLSELECTED('Table'),
        [Series] = CurrentSeries
        && [Scenario] = CurrentScenario
        && [Year] IN YearRange
    )
)

Put this measure in table visual, here is my preview:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[speedramps]

We want to help you but your description is too vague. Please write it again clearly.

Please DON'T copy & paste your DAX that does not work and expect us to fathom what you want to do. That is a bit crazy. ‌‌

Please DO just give a simple non technical functional description of what you want, then let us suggest the solution. Thank you.

Thanks for providing example input data as table text (not a screen print) so we can import the data to build a solution for you.

 
Please provide the example desired output, with a clear step-by-step description of calculations the process flow.
How so you derive 105 from 106 and 99 ????  

Avoid your own bsuiness and system jargon like Series and Moving Average.
Use termninology we all understand. Keep it simple. 

Remember not to share private data ... we don't want you to get into trouble. ‌‌
Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
Try keep it simple and ask one question per ticket.
You will get a quick response if you put time, care and effort into writing clear problem descriptions with example data.

Look forward to helping you when the above information is forthcoming