Forum Discussion
Solved
Rolling average exclude future dates
I'm looking for some assistance in creating a "4-week" rolling average. It's actually going to be a 4-data point rolling average as my snapshots are not always exactly 4-weeks apart. My data has goal...
Sorry. I thought [Amount] was a measure in your original shown table. You can try to replace that with ISBLANK(SUM('CW Dataset'[Amount])).
Regards,
Pat
rnoble
Try your measure with this modification":
4-week average =
VAR last4weeksofsales =
CALCULATETABLE (
TOPN (
4,
SUMMARIZE (
'CW Dataset',
'CW Dataset'[As of date],
"Amount", SUM ( 'CW Dataset'[Amount] )
),
'CW Dataset'[As of date], DESC
),
FILTER (
ALL ( 'Date'[As of date] ),
'Date'[As of date] <= SELECTEDVALUE ( 'Date'[As of date] )
)
)
var __Amount = SUMX ( last4weeksofsales, [Amount] )
RETURN
IF(
ISBLANK(__Amount),
BLANK(),
DIVIDE ( __Amount, COUNTROWS ( last4weeksofsales ) )
)________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
