Last N Weeks Comparisons (DAX Tutorial) (Last 4 weeks, Last 8 weeks, …, Last N weeks)

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