Forum Discussion
Solved
Dax Command for Rolling 3 Week Average
I'm looking to calculate a rolling 3 week average on my GSV for the last 3 weeks but need it to lag a week. For the screen shot I would want an average for weeks 10,9 and 8 and when 11 is completed ...
Hi brockry1 please check this
Rolling_3_Week_Avg =
VAR CurrentWeek = MAX('Sales'[Sales Week])
VAR LastCompletedWeek = CurrentWeek - 1
VAR AvailableWeeks =
TOPN(3,
FILTER(ALL('Sales'), 'Sales'[Sales Week] <= LastCompletedWeek),
'Sales'[Sales Week], DESC
)VAR AvgGSV =
AVERAGEX(AvailableWeeks, 'Sales'[GSV])RETURN
AvgGSV
Hi brockry1 please check this
Rolling_3_Week_Avg =
VAR CurrentWeek = MAX('Sales'[Sales Week])
VAR LastCompletedWeek = CurrentWeek - 1
VAR AvailableWeeks =
TOPN(3,
FILTER(ALL('Sales'), 'Sales'[Sales Week] <= LastCompletedWeek),
'Sales'[Sales Week], DESC
)
VAR AvgGSV =
AVERAGEX(AvailableWeeks, 'Sales'[GSV])
RETURN
AvgGSV