I recently implemented rolling 12 month and rolling 6 week measures in a SSAS tabular cube for product registration counts.

The rolling 12 month calculation is straight-forward and can be implemented using built-in DAX formulas.  This implementation is based off of Alberto Ferrari’s blog post. http://www.sqlbi.com/articles/rolling-12-months-average-in-dax/.

Registration Count Rolling 12 Mos:=CALCULATE([Registration Count Current Period], DATESBETWEEN('Date'[Date], SAMEPERIODLASTYEAR(NEXTDAY(LASTDATE('Date'[Date]))),LASTDATE('Date'[Date])))

The LASTDATE function returns the last date contained within the active filter context of the cube.  For example, if a user has filtered to July 2015 then last date will return July 31, 2015 based on a standard calendar implementation.  The function SAMEPERIODLASTYEAR is using the day after the last date in our filtered dataset and identifies the same date in the prior year.  DATESBETWEEN then identifies all the dates between last year’s and this year’s date.

The rolling 6 weeks implementation was less straightforward. I ended up implementing this calculation using a combination of a calculated column and measure in the cube.

First, I added a column called ‘ThisSaturday’ to the Date table in the cube.  For each date in the Date table, it calculates Saturday’s date in the same week.

The calculated column formula is listed below:

=CALCULATE(MIN('Date'[Date]), FILTER(ALL('Date’),’Date'[Day of Week]=7 && 'Date'[Date] >= EARLIER('Date'[Date])))

We’re basically using the FILTER function to retrieve all the Saturday’s that are on or after the present date and then the MIN function identifies the Saturday that is nearest to the date.

Once we have this calculated column added to the date table, we can then apply it in our rolling 6 week measure.

Registration Count Rolling 6 Wks:=CALCULATE([Registration Count Current Period], DATESBETWEEN('Date'[Date], LASTDATE('Date'[ThisSaturday])-41,LASTDATE('Date'[ThisSaturday])))

In this measure, we use LASTDATE to identify the last date associated to the current filter context.  We then subtract 41 to calculate the date 6-weeks prior and use DATESBETWEEN to retrieve all the dates between this date and the Saturday of the last date in the filter context.