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