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?
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,
Maybe you can share a sample of your model with us.
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