Forum Discussion
Moving daily average with offset
Hello PBI Community!
I am trying to produce a 21-day moving average with a 7-day offset. The DAX formula below gets me close. In this instance the offset (7 days) is applied however the 21-day moving average only comes in at 15 days. If I make it -27 the output is then a 21-day moving average. I would prefer to have an accurate representation of the moving average interval. What am I overlooking?
Thank you all!
21-day moving avg with 7-day offset :=var sum_dist =
CALCULATE (
[Total Dist],
FILTER (
ALL ( dDateSeason ),
dDateSeason[Date]
<= MAX ( dDateSeason[Date] ) - 7
&& dDateSeason[Date]
>= MAX ( dDateSeason[Date] ) - 21
)
)
var count_days_dist =
CALCULATE (
DISTINCTCOUNT ( dDateSeason[Date] ),
FILTER (
ALL ( dDateSeason ),
dDateSeason[Date]
<= MAX ( dDateSeason[Date] ) - 7
&& dDateSeason[Date]
>= MAX ( dDateSeason[Date] ) - 21
)
)
return
DIVIDE(sum_dist,count_days_dist)
sdgiss If you want the average to span 21 days, then it will need to be 27 days ago to 7 days ago (since you are use = on both ends it is inclusive, otherwise you'd need to use 28).
When you say 21 day moving average offset, what are you wanting to acheive? I think the 27 (or 28 without 😃 is what you're looking for?
9 Replies
sdgiss , Try like this , with help from a date table
Rolling 21 = CALCULATE(count(Sales[Serial Number]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ])-7,-21,DAY))
Thank you for your help amitchandak! I had thought of this as an alternative but got stuck on my formula above. When in doubt, I should always choose the path of least resistance. Thank you for pointing this out with your solution!
Hello amitchandak!
The formula above yields the appopropriate output, but unfortnately it doesn't work as a measure inside the following. It is usable in other measures but I'm guessing the PREVIOUSDAY function doesn't like it.
=CALCULATE([Rollling 21-day avg],PREVIOUSDAY(dDateSeason[Date]))
This is the error I receive when attempting to load it a pivot table.
sdgiss If you want the average to span 21 days, then it will need to be 27 days ago to 7 days ago (since you are use = on both ends it is inclusive, otherwise you'd need to use 28).
When you say 21 day moving average offset, what are you wanting to acheive? I think the 27 (or 28 without 😃 is what you're looking for?
Thank you so much for your reply AllisonKennedy! It turns out I was making this much harder than it had to be! The solution from amitchandak works like a charm. Many thanks again for offering up your assistance!
sdgiss Glad you got what you're looking for. amitchandak solution should yield the same results as your solution - they are just two different ways to write/express the same calculation (depending on how you want to think about it). In your original one, you were defining the start and end dates. In Amit's he is defining the start date and how long the period should be.
Love kudos if I helped.
