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
