Forum Discussion

ClemFandango's avatar
ClemFandango
Icon for Advocate II rankAdvocate II
3 years ago
Solved

Moving annual average by 3 months

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

 

YearMonthMonthly Total
2020February56
2020April15
2021March49
2021July72
2021April59
2021December31
2021November97
2021January57
2021October51
2021September64
2021May78
2021June64
2021August24
2021February33
2022September63
2022January33
2022April40
"Date Table"
"DAX"
Average last 3 month
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    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:

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    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: