We often need to compare our current week’s sales with the average sales over the last 4, 8, 52 or even N weeks. In this tutorial I go over one of the possible approaches that will allow you to provide a drop-down choice for the user to select the time range to compare with the current week. We will also create a smart measure that will calculate the average sales over the selected N weeks.
You can download the tutorial materials here.
Avg Last N Weeks Sales =
var SelectedWeekOptionsValue = SELECTEDVALUE(WeeksOptions[Weeks#])
var CurrentYearWeek = SELECTEDVALUE('Date'[YearWeek])
var AvgLastNWeeksSales =
AVERAGEX(
ADDCOLUMNS(
TOPN(
SelectedWeekOptionsValue,
CALCULATETABLE(
VALUES('Date'[YearWeek]),
'Date'[YearWeek]<CurrentYearWeek),
'Date'[YearWeek],
DESC
),
"tempSales",
[Sales]
),
[tempSales]
)
return AvgLastNWeeksSales
business intelligist 
Thanks for awesome tutorial. Made some reports really nice and easy to read. 🙂
I’m trying to apply same approach for %, but it looks like it’s not working as i get only current %. Is it meant to work with % as well or i should look for different approach?