There are a number of business analysis calculations, such as forecasting, that often require an analysis of a sliding scale of the data from previous (complete) date periods. For example, it is fairly common that the last 3 complete months' worth of data is used to forecast the current month. In other words, if today's date is April 6, 2020, then the dates desired would be from January 1, 2020 to March 31, 2020. These sliding scales must be able to cross year boundaries as well as ensure that the date ranges calculated are exact. Being off by even a single day can sometimes greatly affect forecasts.

While Power BI has a DAX rolling average quick measure, this measure only works with days, months, quarters, and years. The reason is that DAX's time intelligence generally does not support weeks, and for good reason. Dealing with weeks tends to get tricky. This recipe demonstrates how to compute calculations that utilize rolling weeks but be warned; this is probably the most complex recipe in this chapter!

To prepare for this recipe, perform the following steps:

1. Create a table called R09_Sales by importing the Sales.csv file from the GitHub repository here: https://github.com/PacktPublishing/DAX-Cookbook/tree/master/Chapter02.

To implement this recipe, perform the following steps:

1. Create the following measure:

Rolling Week Average = 
    VAR __Date = MAX('R09_Sales'[Date])
    VAR __Sales = ALL('R09_Sales')
    VAR __WeeksBack = 4
    VAR __WeeksForward = 0
    VAR __RollingWeekStartDate = __Date - 7 * __WeeksBack
    VAR __RollingWeekEndDate = (__Date + 7 * __WeeksForward) - 1
RETURN
    AVERAGEX(
        FILTER(
            __Sales,
            [Date] >= __RollingWeekStartDate &&
                [Date] <= __RollingWeekEndDate
        ),
        [Value]
    )

2. Create a Line Chart visualization and place the Date column from the R09_Sales table in the Axis field and the Rolling Week Average measure in the Values field.

We begin by initializing the date we are working with, __Date, and the table where we will be averaging values, __Sales. We then set how many weeks backward and forward that we want to include in our averages for any particular day and store these in the variables __WeeksBack and __WeeksForward, respectively.

We can use these variables to calculate the start and end dates of our rolling weeks in the __RollingWeekStartDate and __RollingWeekEndDate variables. In the case of __RollingWeekStartDate, we simply need to use date subtraction to subtract the number of __WeeksBack variable by 7 (seven days in a week). For __RollingWeekEndDate, it is a similar calculation except that we are adding days and we subtract 1 in order to not include the current date.

Once we have the dates to include, we FILTER the table to only include dates within and including our __RollingWeekStartDate and __RollingWeekEndDate variables and use AVERAGEX to average the Value column.

Now, there are a couple of issues associated with our rolling week average calculation. First, this calculation does not look very complex at all despite the warning at the beginning of this recipe! Second, and most importantly, this really is not a rolling four week average; it is more like a rolling 28 day average. Fear not. This initial calculation is simply an example to explain the concept of rolling averages. In short, the entire crux of the issue is to calculate the start and end dates for our rolling window of dates and use those to filter our table such that only the values within that date range are included in our calculation.

What we really want, however, is to include the last four full weeks of values for any particular date. To do this, we will need some help from our Finding week start and end dates recipe. We can use that recipe to adjust our calculation for __RollingWeekStartDate and __RollingWeekEndDate . To do this, create a measure from the code located in the R09_RollingWeekAverage2.txt file in the GitHub repository.

OK. You were warned that this was complex! So, let's break this calculation down. The first four lines are largely the same except, since we are only interested in full weeks, we set our __WeeksForward variable to -1 to ensure that the last full week, and not the current week, is included in the final range of dates.

The majority of the remainder of the calculation is dominated by the creation of a calendar table variable, __Calendar. This variable contains a calendar that will be used by the subsequent steps in the calculation and is central to the entire solution. The creation of this variable begins with the creation of a calendar table that contains a range of date values based on our __WeeksBack and __WeeksForward variables. To this table we then add two columns, __WeekStarting and __WeekEnding, based on our Finding week start and end dates recipe. For a full explanation of these two columns, refer to the Finding week start and end dates recipe. There are no real changes required other than those required to fit them into the formula as essentially nested VAR statements. You should observe that any formula for computing these two columns could be inserted, such as finding the first and last working days of a week.

Once we have our calendar table with our __WeekStarting and __WeekEnding columns, we can now create the dates whose __WeekStarting and __WeekEnding dates we wish to look up. These variables are __LookupDateStart and __LookupDateEnd, which are essentially the same calculation we used earlier in our original formula for Rolling Week Average when computing __RollingWeekStartDate and __RollingWeekEndDate. This time, when computing __RollingWeekStartDate and __RollingWeekEndDate, we use the __LookupDateStart and __LookupDateEnd variables to find the corresponding dates in our __Calendar table and return either the __WeekStarting or __WeekEnding dates, respectively.

Once we have our __RollingWeekStartDate and __RollingWeekEndDate values, the remainder of our formula is the same as before.

For more details regarding the functions in this recipe, refer to the following links:

• ADDCOLUMNS: https://docs.microsoft.com/en-us/dax/addcolumns-function-dax
• FILTER: https://docs.microsoft.com/en-us/dax/filter-function-dax
• CALENDAR: https://docs.microsoft.com/en-us/dax/calendar-function-dax
• AVERAGEX: https://docs.microsoft.com/en-us/dax/averagex-function-dax