Moving daily average with offset

[sdgiss]

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)

[amitchandak]

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

[sdgiss]

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]

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!

[AllisonKennedy · Accepted solution]

@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?

[sdgiss]

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!

[AllisonKennedy]

@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. 

[sdgiss]

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!

[AllisonKennedy]

@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!

[sdgiss]

@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!