Forum Discussion
brockry1
Helper II
Helper IIDax 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
brockry1Helper II
Helper IISo I do have a Calendar Table:
And have a many to one relationship on date to date for my Fact Table to Calendar.
So the end of week in the sample isn't working.
Jihwan_Kim
Super User
Super UserHi,
I still need to understand how your calendar dimension table is structured, for instance, what column is sorted by what column.
Please share your sample pbix file's link, and then, I can try to look into it.
Thank you.