Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Hi @jockeywockey,
Maybe you can share a sample of your model with us.
Best Regards,
Dale
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |