Implementing a rolling x month or x week calculation in MDX is relatively straightforward if you have a calendar hierarchy in the multi-dimensional cube.  In some cases, you may only want to include dates where a measure is non-empty.  For example, if a user selects the current month, you may not want to include any future weeks that are empty in the rolling calculation.

Here’s some MDX that implements a rolling 6-week calculation that only includes non-empty dates.

Ancestor(Tail(NonEmpty(Descendants(StrToMember(@Calendar),[Date].[Calendar].[Date]),[Measures].[Registration Count])).Item(0),[Date].[Calendar].[Calendar Week of Year]).Lag(6):Ancestor(Tail(NonEmpty(Descendants(StrToMember(@Calendar),[Date].[Calendar].[Date]),[Measures].[Registration Count])).Item(0),[Date].[Calendar].[Calendar Week of Year])

This implementation uses the DESCENDANTS function to identify all child dates based on the current position in the Calendar hierarchy (represented by the parameter @Calendar).  We use the TAIL function to return the last date where the [Registration Count] measure is non-empty; then the ANCESTOR function returns the [Calendar Week of Year] associated to the date returned by the TAIL function. 

Once we identify the week based on the current position in the calendar hierarchy we can then use LAG(6) to navigate 6 weeks back.  The Range operator ‘:’ returns all the weeks from 6 weeks prior to the current week based on the calendar hierarchy position.

Here’s a complete MDX query using this logic.  Note: I’ve replaced StrToMember(@Calendar) with the member [Date].[Calendar].[Calendar Year].&[2015].&[Q2 2015].&[Apr 2015].

SELECT {[Measures].[Registration Count]} ON COLUMNS,  NON EMPTY [Date].[Calendar Week of Year].[Calendar Week of Year].MEMBERS ON ROWS  
FROM [Cube]  
WHERE Ancestor(Tail(NonEmpty(DESCENDANTS([Date].[Calendar].[Calendar Year].&[2015].&[Q2 2015].&[Apr 2015],[Date].[Calendar].[Date]),[Measures].[Registration Count])).ITEM(0),[Date].[Calendar].[Calendar Week of Year]).LAG(6):Ancestor(Tail(NonEmpty(DESCENDANTS([Date].[Calendar].[Calendar Year].&[2015].&[Q2 2015].&[Apr 2015],[Date].[Calendar].[Date]),[Measures].[Registration Count])).ITEM(0),[Date].[Calendar].[Calendar Week of Year])