Optimizing Prior Year Calculation

We have a unique prior year calculation in which we only want to compare where we are in the current time period to the parallel period. For example if we are in the fixcal week hierarchy and it is wednesday, we only want to compare the first up to wednesday in the parallel period.

The following code works, but is pretty slow. I was wondering what we can do to optimize the following mdx.

IIF(CDate(Left(Right(Head(Descendants([Date Dimension].[Fiscal Year].CurrentMember,,LEAVES)).Item(0).UniqueName,20),10)) > VBA![Date](), -- is the current member completely in the future
	NULL, -- completely future period
	IIF(CDate(Left(Right(Head(Descendants([Date Dimension].[Fiscal Year].CurrentMember,,LEAVES)).Item(0).UniqueName,20),10)) < CDate("2010-09-27"), -- is the current member completely in the past
		NULL
	,  -- no back data loaded
		IIF([Date Dimension].[Fiscal Year].LEVEL.ORDINAL=1, --year
			IIF(CDate(Left(Right(Head(Descendants([Date Dimension].[Fiscal Year].CurrentMember,,LEAVES)).Item(0).UniqueName,20),10)) < vba![Date]() -- is the current member part in the past and part in the future
			AND CDate(Left(Right(Tail(Descendants([Date Dimension].[Fiscal Year].CurrentMember,,LEAVES)).Item(0).UniqueName,20),10)) > vba![Date](),
				Sum(
				Descendants(ParallelPeriod([Date Dimension].[Fiscal Year].[FY Year Name], 1, [Date Dimension].[Fiscal Year].CurrentMember),,LEAVES).Item(0)
					:
				ParallelPeriod([Date Dimension].[Fiscal Year].[Day Name], 364, StrToMember("[Date Dimension].[Fiscal Year].[Day Name].&[" + CSTR(Year(vba![Date]())) + "-" + IIF(Len(CSTR(Month(vba![Date]())))=1,"0","") + CSTR(Month(vba![Date]())) + "-" + IIF(Len(CSTR(Day(vba![Date]())))=1,"0","") + CSTR(Day(vba![Date]())) + "T00:00:00]")) 
				, [Measures].[Dollars])
			,
				(ParallelPeriod([Date Dimension].[Fiscal Year].[FY Year Name], 1, [Date Dimension].[Fiscal Year].CurrentMember), [Measures].[Dollars]) --get parallel day and then create a tuple with [Measures].[Dollars]
			)
		,-- end year
			IIF([Date Dimension].[Fiscal Year].LEVEL.ORDINAL=2, --quarter
				IIF(CDate(Left(Right(Head(Descendants([Date Dimension].[Fiscal Year].CurrentMember,,LEAVES)).Item(0).UniqueName,20),10)) < vba![Date]() -- is the current member part in the past and part in the future
				AND CDate(Left(Right(Tail(Descendants([Date Dimension].[Fiscal Year].CurrentMember,,LEAVES)).Item(0).UniqueName,20),10)) > vba![Date](),
					Sum(
					Descendants(ParallelPeriod([Date Dimension].[Fiscal Year].[FY Quarter Name], 4, [Date Dimension].[Fiscal Year].CurrentMember),,LEAVES).Item(0)
						:
					ParallelPeriod([Date Dimension].[Fiscal Year].[Day Name], 364, StrToMember("[Date Dimension].[Fiscal Year].[Day Name].&[" + CSTR(Year(vba![Date]())) + "-" + IIF(Len(CSTR(Month(vba![Date]())))=1,"0","") + CSTR(Month(vba![Date]())) + "-" + IIF(Len(CSTR(Day(vba![Date]())))=1,"0","") + CSTR(Day(vba![Date]())) + "T00:00:00]")) 
					, [Measures].[Dollars])
				,
					(ParallelPeriod([Date Dimension].[Fiscal Year].[FY Quarter Name], 4, [Date Dimension].[Fiscal Year].CurrentMember), [Measures].[Dollars]) --get parallel day and then create a tuple with [Measures].[Dollars]
				)
			,-- end quarter
				IIF([Date Dimension].[Fiscal Year].LEVEL.ORDINAL=3, --month
					IIF(CDate(Left(Right(Head(Descendants([Date Dimension].[Fiscal Year].CurrentMember,,LEAVES)).Item(0).UniqueName,20),10)) < vba![Date]() -- is the current member part in the past and part in the future
					AND CDate(Left(Right(Tail(Descendants([Date Dimension].[Fiscal Year].CurrentMember,,LEAVES)).Item(0).UniqueName,20),10)) > vba![Date](),
						Sum(
						Descendants(ParallelPeriod([Date Dimension].[Fiscal Year].[FY Month Name], 12, [Date Dimension].[Fiscal Year].CurrentMember),,LEAVES).Item(0)
							:
						ParallelPeriod([Date Dimension].[Fiscal Year].[Day Name], 364, StrToMember("[Date Dimension].[Fiscal Year].[Day Name].&[" + CSTR(Year(vba![Date]())) + "-" + IIF(Len(CSTR(Month(vba![Date]())))=1,"0","") + CSTR(Month(vba![Date]())) + "-" + IIF(Len(CSTR(Day(vba![Date]())))=1,"0","") + CSTR(Day(vba![Date]())) + "T00:00:00]")) 
						, [Measures].[Dollars])
					,
						(ParallelPeriod([Date Dimension].[Fiscal Year].[FY Month Name], 12, [Date Dimension].[Fiscal Year].CurrentMember), [Measures].[Dollars]) --get parallel day and then create a tuple with [Measures].[Dollars]
					)
				,-- end month
					IIF([Date Dimension].[Fiscal Year].LEVEL.ORDINAL=4, --week
						IIF(CDate(Left(Right(Head(Descendants([Date Dimension].[Fiscal Year].CurrentMember,,LEAVES)).Item(0).UniqueName,20),10)) < vba![Date]() -- is the current member part in the past and part in the future
						AND CDate(Left(Right(Tail(Descendants([Date Dimension].[Fiscal Year].CurrentMember,,LEAVES)).Item(0).UniqueName,20),10)) > vba![Date](),
							Sum(
							Descendants(ParallelPeriod([Date Dimension].[Fiscal Year].[FY Week Name], 52, [Date Dimension].[Fiscal Year].CurrentMember),,LEAVES).Item(0)
								:
							ParallelPeriod([Date Dimension].[Fiscal Year].[Day Name], 364, StrToMember("[Date Dimension].[Fiscal Year].[Day Name].&[" + CSTR(Year(vba![Date]())) + "-" + IIF(Len(CSTR(Month(vba![Date]())))=1,"0","") + CSTR(Month(vba![Date]())) + "-" + IIF(Len(CSTR(Day(vba![Date]())))=1,"0","") + CSTR(Day(vba![Date]())) + "T00:00:00]")) 
							, [Measures].[Dollars])
						,
							(ParallelPeriod([Date Dimension].[Fiscal Year].[FY Week Name], 52, [Date Dimension].[Fiscal Year].CurrentMember), [Measures].[Dollars]) --get parallel day and then create a tuple with [Measures].[Dollars]
						)
					,-- end week
					-- day - always use the regular 364 day parallel calculation
						(ParallelPeriod([Date Dimension].[Fiscal Year].[Day Name], 364, [Date Dimension].[Fiscal Year].CurrentMember), [Measures].[Dollars]) --get parallel day and then create a tuple with [Measures].[Dollars]
					)
				)
			)
		)
	)
), 
FORMAT_STRING = "$#,##0.00;($#,##0.00)", 
VISIBLE = 1 ,  DISPLAY_FOLDER = 'Year Over Year' ,  ASSOCIATED_MEASURE_GROUP = 'Line Item';

Without looking at your code in a lot of detail here are some things I’d try:

1)      Call the VBA Date function once and create a named set of one date member that represents the Current Date or Today.  Use this date member e.g [Current Date].Item(0) in your calculation instead of all the VBA calls.  See example here: http://www.bidn.com/forums/microsoft-business-intelligence/analysis-services/631/named-set-mdx-script-for-current-month   The whole StrToMember() clusters of code could be replaced by [Current Date].Item(0)

2)      Make sure the member value in the properties of your date attribute is assigned an SQL DateTime value and reference it using .membervalue any time you need it instead of using CDate functions and string manipulation functions on the UniqueName.  Preferably use an integer like 20111228 for the date key property.  Set the name property to your preferred date format (e.g. Dec 28.2011). 

3)      Break up the calculation into smaller more manageable units.  I think the whole calculation could be simplified into individual Year-To-Date Prior Year (YTD PY), Quarter-To-Date Prior Year (QTD PY), Month-To-Date Prior Year (MTD PY), and Week-To-Date Prior Year (WTD PY) calculations.   Use SCOPE to overwrite your measure using the results of these simpler calculations to keep it as one measure.

4)      Use SCOPE assignment to overwrite this calculation with NULL where it intersects with all the past and future date ranges that are not applicable.  Do this at all levels of your date hierarchy.  This removes the necessity to do the date comparisons at the top of your code every time you execute the calculation.

Here's an simplified example for future dates at the day level:

//Create set of date members that is Tomorrow through the last date in the cube
CREATE HIDDEN SET CurrentCube.[Future Dates in Cube] AS [Current Date].Item(0).lead(1) : NULL;

//At Day level assign NULL to these measures for future dates
Scope ({[Measure].[Your Measure]
,[Measure].[YTD PY]
,[Measure].[QTD PY]
,[Measure].[MTD PY]
,[Measure].[WTD PY] }
,[Future Dates in Cube]
);
  this = NULL;
End Scope;

5)      Use SCOPE to replace IIF whenever possible

Hope you find some helpful information here. 

Luke