Need to create Average of averages from another measure

[amoatasem]

Hi

I'm facing an issue where I want to create an average measure from another measure.

Basically, I'm trying to calculate 2 month average using the following: 

2 Mth Avg =

AVERAGEX (

    DATESINPERIOD ( 'DB'[Month], MAX ( 'DB'[Month] ), -2, MONTH ),

    CALCULATE ( SUM ( 'DB'[Value] ) )

)

which works fine, then I want to calculate 12 month average from the 2 month measure to further smooth the data points using:

12 Mth Avg =

AVERAGEX (

    DATESINPERIOD ( 'DB'[Month], MAX ( 'DB'[Month] ), -12, MONTH ),

    CALCULATE ( SUMX('DB',[2 Mth Avg])

)

)

But, I end up with the 12 month average of the previous 12 data points of value (68.15) while it should be (67.78) if it's calculated from the 2 mth avg column.

[talespin]

hi @amoatasem 

SumVal is a measure SUM(Value)

Please see if this helps.

2MthAvg =

AVERAGEX(

            AvgAvg,

            CALCULATE( AVERAGEX(AvgAvg,[SumVal]), REMOVEFILTERS(), DATESINPERIOD( AvgAvg[Date], MAX(AvgAvg[Date]), -2, MONTH) )

)

12MthAvg =

AVERAGEX(

            AvgAvg,

            CALCULATE( AVERAGEX(AvgAvg,[2MthAvg]), REMOVEFILTERS(), DATESINPERIOD( AvgAvg[Date], MAX(AvgAvg[Date]), -12, MONTH) )

)

[amoatasem]

Hi @talespin 

Thank you for your reply.

For some reason I can't produce the same, when I try to calculate 2mnth Avg with the same equation I end up with the below:

[AnalyticPulse · Accepted solution]

12 Mth Avg = AVERAGEX ( DATESINPERIOD ( 'DB'[Month], MAX ( 'DB'[Month] ), -12, MONTH ), [2 Mth Avg] )
can you try this and let me know if this works

[amoatasem]

It works, thank you very much!

[AnalyticPulse]

great, you can give kudos and accept my reply as a solution 

[amoatasem]

To be more clear

This is what I want to achieve

Year	Month	Value	2 mth avg	12 mth avg
2023	December	57.85	61.68	68.94
2024	January	59.04	58.45	68.07
2024	February	59.74	59.39	67.33
2024	March	70.33	65.04	66.89
2024	April	73.25	71.79	67.00
2024	May	73.71	73.48	67.14
2024	June	72.39	73.05	67.39
2024	July	69.43	70.91	67.51
2024	August	67.20	68.32	67.10
2024	September	68.38	67.79	66.92
2024	October	69.40	68.89	67.10
2024	November	68.18	68.79	67.30
2024	December	66.71	67.45	67.78