topic Re: Moving daily average with offset in DAX Commands and Tips

Moving daily average with offset

sdgiss — Wed, 30 Dec 2020 21:45:07 GMT

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)

Re: Moving daily average with offset

AllisonKennedy — Wed, 30 Dec 2020 22:05:14 GMT

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?

Re: Moving daily average with offset

amitchandak — Thu, 31 Dec 2020 02:50:06 GMT

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))

Re: Moving daily average with offset

sdgiss — Thu, 31 Dec 2020 14:43:33 GMT

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!

Re: Moving daily average with offset

sdgiss — Thu, 31 Dec 2020 14:45:21 GMT

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!

Re: Moving daily average with offset

sdgiss — Thu, 31 Dec 2020 17:45:31 GMT

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.

Re: Moving daily average with offset

AllisonKennedy — Fri, 01 Jan 2021 04:38:29 GMT

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.

Re: Moving daily average with offset

sdgiss — Fri, 01 Jan 2021 18:54:04 GMT

You were a great help AllisonKennedy! You helped confirm that my first formula might be the one to move forward with. The one from amitchandak is more straightforward and convenient but it doesn't work as a measure with time intelligence functions. If I can't find a way to resolve this issue then I will likely elevate your response to the solution.

Thank you, and Happy New Year!

Re: Moving daily average with offset

AllisonKennedy — Sun, 03 Jan 2021 00:24:05 GMT

sdgiss What do you mean when you say 'it doesn't work as a measure with time intelligence functions'. If you can add more detail on what's not working, we can hopefully help you resolve the issue. 🙂 Happy New Year to you too!

Re: Moving daily average with offset

sdgiss — Tue, 16 Mar 2021 18:20:49 GMT

AllisonKennedy my sincere apologies for not responding to this sooner. It has been a hectic few months.

Did you see the error message when trying to use the suggestion from Amit? I have the measure in there as well. When I place the original equation (the one you validated for me) I have no issues with the measure output, however with Amit's suggestion it makes the measure unusable.

Thank you!