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

3 Replies

  • 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

    • jockeywockey's avatar
      jockeywockey
      Advocate I
      8 years ago

      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