There's more...

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.