Moving annual average by 3 months

[ClemFandango]

Hi there,

I have the following date table by year and month. It includes dates from 2020 and is continually updated.

I am trying to create an additional column called MAA that calculates the rolling average of the 'Monthly Total'  every 3 months, but failing terribly. Any ideas?

Any help greatly appreciated

Year	Month	Monthly Total
2020	February	56
2020	April	15
2021	March	49
2021	July	72
2021	April	59
2021	December	31
2021	November	97
2021	January	57
2021	October	51
2021	September	64
2021	May	78
2021	June	64
2021	August	24
2021	February	33
2022	September	63
2022	January	33
2022	April	40

[ClemFandango]

This is fantastic @FreemanZ 

Big thanks

[FreemanZ · Accepted solution]

hi @ClemFandango 

not sure if i fully get you. 

1) add a calculated column like:

Date = 
VAR _month =
SWITCH(
    [month],
    "January", 1,
    "February", 2,
    "March", 3,
    "April", 4,
    "May", 5,
    "June", 6,
    "July", 7,
    "August", 8,
    "September", 9,
    "October", 10,
    "November", 11,
    12
)
RETURN
DATE([Year], _month, 1)

(or do it in Power Query Editor)

2) add a calculated column like:

Column = 
AVERAGEX(
    TOPN(
        3,
        FILTER(    
            data,
            data[date]<=EARLIER(data[date])
        ),
        data[date]
    ),
    [Monthly Total]
)

it worked like: