These examples show how to create period based views that display rolling “N” periods. This is done by creating a level filter on a time hierarchy. The time range property for the view in both examples was set to No.
The first view is a rolling 12 weeks. It has rows comprised of the two levels from the Year Weeks time hierarchy. The Weeks level has a Rolling “12” filter on it that calculates the current week and prior 11 weeks. The filter returns Week 38 back through Week 27 in this example.
The expression for the level filter is:
COUNT(EXISTS([Time].[Year Weeks].CurrentMember,LASTPERIODS(12,EXISTS([Time].[Year Weeks].[Weeks].members,[Time].[Year Based Weeks Based].[Year Based].[Current Year].[Current Week]).Item(0))))
This expression uses the Year Based Weeks Based hierarchy to determine the Current Week, Current Year.
A simple change to the expression changes the view to a rolling 52 weeks. Change the 12 in the expression to 52. It will calculate the current week and prior 51 weeks.
COUNT(EXISTS([Time].[Year Weeks].CurrentMember,LASTPERIODS(52,EXISTS([Time].[Year Weeks].[Weeks].members,[Time].[Year Based Weeks Based].[Year Based].[Current Year].[Current Week]).Item(0))))
Here is the refreshed view after making that change. The filter returns Week 38 of 2014 through Week 39 of 2013.
If you want to see rolling periods in the future, use a negative number for the LASTPERIODS part of the expression. Here is the expression when the 52 is changed to -52. It will calculate the current week and next 51 weeks.
COUNT(EXISTS([Time].[Year Weeks].CurrentMember,LASTPERIODS(-52,EXISTS([Time].[Year Weeks].[Weeks].members,[Time].[Year Based Weeks Based].[Year Based].[Current Year].[Current Week]).Item(0))))
Here is the refreshed view after making that change. The filter returns Week 37 of 2015 through Week 38 of 2014. The future weeks for actual sales do not have data yet, so those cells are empty in the view. This type of filter would be useful in views that contain measures that have anticipated future data, such as budget or forecast measures.
