One Client I was working with wanted a named set that would give them the last 5 years. Of course this needed to be a rolling 5 years so it automatically moved the years up every January.

To create this I used the StrToMember function in MDX.

First To create a basic date range in a named set the syntax would be:

[Admit Date].[Year].[2005]:[Admit Date].[Year].[2010]

But this would require a manual change every year. To make the Named Set a Rolling 5 years you will need to use a few functions.

StrToMember() = Convert a String to a member that the MDX calculation can use in a tuple or set

Now() = Gets the date and time at the moment it is call

Year() = Gets the year of the given date

DateAdd() = Subtracts or adds to a date

CStr() = Converts a number to a string

So you will need to build two members, one for the current year and one for 5 years ago.

The current year syntax is:

strtomember(“[Admit Date].[Year].[" + cstr(year(now())) +"]“)

Notice the strToMember surrounds the entire statement. Then you have year getting the year of the current date. The CSTR function is converting the year to a string so it can be combined with the rest of the string. The strToMember the converts this all to a valid member.

The next member is the year 5 years ago. This is just like the current year member except you use the DateAdd function to subtract 5 years.

The syntax for the second member is:

strtomember(“[Admit Date].[Year].[" + cstr(year(dateadd('yyyy',-5,now()))) +"]“)

Now just take the first and second members and place curly brackets “{}” around them with a colon “:” in the middle. The end result will look like so:

{strtomember(“[Admit Date].[Year].[" + cstr(year(dateadd('yyyy',-5,now()))) +"]“):strtomember(“[Admit Date].[Year].[" + cstr(year(now())) +"]“)}