Forum Discussion

sdgiss's avatar
sdgiss
Helper I
5 years ago
Solved

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 mo...
  • AllisonKennedy's avatar
    AllisonKennedy
    5 years ago

    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?

amitchandak's avatar
amitchandak
Super User
5 years ago

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's avatar
sdgiss
Helper I
5 years ago

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.