cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jockeywockey Frequent Visitor
Frequent Visitor

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 functionality. could somebody help with the solution to this please?

3 REPLIES 3
Anonymous
Not applicable

Re: Moving Average

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:

 

PeriodTable.PNG

 

Hope this helps,

Parker

Community Support Team
Community Support Team

Re: Moving Average

Hi @jockeywockey,

 

Maybe you can share a sample of your model with us.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jockeywockey Frequent Visitor
Frequent Visitor

Re: Moving Average

Hi, thanks for the reply.  I have not had chance to fully look at this but I will come back shortly with if the original reply worked but if not I will post a sample file to explain what my request is