Forum Discussion

jockeywockey's avatar
jockeywockey
Advocate I
8 years ago

Moving Average

potential simple question but we split the year into 13 periods (2018/19 P01, for example) and so would like a Moving Average of the last 13 periods but all DAX i have seen is based on date functiona...
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hey jockeywockey

 

Try creating a measure like this:

 

13PeriodMovingAverage = 
VAR CurrentPeriod = FIRSTNONBLANK(Sales[Period],1)
RETURN
CALCULATE(
    SUM(Sales[SalesAmount]),
    FILTER(
        FILTER(ALLSELECTED(Sales), Sales[Period] <= CurrentPeriod),
        Sales[Period] > CurrentPeriod - 13
    )
)
/
CALCULATE(
    DISTINCTCOUNT(Sales[Period]),
    FILTER(
        FILTER(ALLSELECTED(Sales), Sales[Period] <= CurrentPeriod),
        Sales[Period] > CurrentPeriod - 13
    )
)

This works if your periods are numeric so you may have to figure out how to convert your text periods to whole numbers. See below:

 

 

Hope this helps,

Parker