Dynamic, date related, subsets

Have you ever forgotten to roll over your date related subsets or would you like them to automatically roll with the punches dates?

In yet another date related post, this time the focus is on creating dynamic subsets for a financial year and month dimension by leveraging the calendar cube from a previous post.

There are two steps to this example:

1. Create calendar measure rules

2. Build subsets

1. Create Calendar Measure Rules

This example assumes that you have a calendar cube and we are going to use the Days In Month element.

Against this dimension we need to create two text attributes:

• Cur_FinYear

• Cur_Mth

Assuming the dimension is called Calendar measure, then the rules are placed into the }elementattributes_calendar measure cube.

# Create value against Cur_Mth attribute based on current date
['Days in month', 'Cur_Mth'] = S:  TIMST( NOW(), '\M');

# Create value against Cur_Year attribute based on current date (note the 7 determines the starting period for the financial year)
['Days in month', 'Cur_FinYear'] = S:
 	If ( Month ( Date ( Now(), 1) ) >= 7
		, Timst ( Now(), '\Y') | '/' | Str ( Year ( Date( Now() ) ) + 2001, 4, 0 )
			,  Str ( Year ( Date( Now() ) ) + 1999, 4, 0) | '/' | Timst( Now(), '\Y'));

2. Build Subsets

Assuming we have two dimensions called Financial Year and Month, we want to create a couple of dynamic subsets and we can call them each Current.

Year

The MDX for the Current subset in the Year dimension is:

{ FILTER( {TM1SUBSETALL([Financial Year])} ,
[Financial Year].CurrentMember.Name = [}elementattributes_Calendar Measure].([Calendar Measure].[Days In Month],[}ElementAttributes_Calendar measure].[cur_finyear]))}

Month

The MDX for the Current subset in the Month dimension is:

{FILTER(  {TM1SUBSETALL([Month])} ,[Month].CurrentMember.Name = [}elementattributes_Calendar Measure].([Calendar Measure].[Days In Month],[}ElementAttributes_Calendar measure].[cur_mth]))}