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
Thanks for the reply mahoneypat! I get an error when I try that: The value for 'Amount' cannot be determined. Either the column doesn't exist, or there is no current row for this column.
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 ) )
)
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