MDX Statement For YTD Last Year

[Adamba]

Hi all,

I have been using the YTD() and PeriodsToDate() functions in MDX to create rolling Year-to-Date calculated members.

My problem arises while trying to use the item(0).lag(1) code to try and get the same YTD() period for a year ago.
The problem with using either the lag function or parallel period is i get a full year figure for 2005 compared to a 2 month figure for 2006. I wish to Compare identical months/weeks on a 'rolling' basis.

The last combination of code i have used to try and get the desired members are as below:

Latest YTD: SUM({ YTD([Time].[Year].&[2006]) })
Previous YTD: SUM({ParallelPeriod(Year,1,[Time].[Year].&[2006])})

Many Thanks in Advance!!

Regards,




Adam

 

[Adamba]

Just For Info, I have now managed to crack this.

The code i have used for Previous Year (Year to Date) is as follows:

SUM({Head(Descendants([Time].[Year].&[2005],2), COUNT(Descendants([Time].[Year].&[2006],2), ExcludeEmpty))})

Maybe not the best solution for rolling over years as will need to be changed in 2007 but it works fine for my needs.

Regards,



Adam