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
One thought on “Last N Weeks Comparisons (DAX Tutorial) (Last 4 weeks, Last 8 weeks, …, Last N weeks)”
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?