More on tuning YTD-style calculations

I’m indebted to Deepak Puri for coming up with a much less complex MDX expression for finding the common ancestors in a set of months. Here it is:

WITH
/*The initial time range expressed in months*/
SET MYRANGE AS ‘{[Time].[1997].[Q1].[3] :[Time].[1998].[Q3].[7]}’
/*Test whether Time member is a subset of range */
Member [Measures].[InRange] asExcept(Descendants([Time].CurrentMember,, LEAVES), MYRANGE).Count = 0′
/* Include all members in range whose parents are not in range */
SET MYNEWRANGE ASFilter([Time].Members, [Measures].[InRange] AND Not ( [Measures].[InRange], [Time].Parent))’
/*Helper calculated members to display the steps*/
MEMBER MEASURES.STEP1 ASSETTOSTR(MYRANGE)’
MEMBER MEASURES.STEP5 ASSETTOSTR(MYNEWRANGE)’
/*Show working*/
SELECT {MEASURES.STEP1, MEASURES.STEP5} ON 0 FROM SALES

%d bloggers like this: