Hello,

I am trying to replace the use of Now() in our MDX cube calculations.   I have seen in multiple performance guides that now() causes calculations to take place at runtime instead of using the cube aggs.       I have been able to do this for simple calculations like filtering the year to last year, but am having trouble with constructing MDX to get a range of values.   I have columns in the date dimension that come from a view which calculate the begin and end of the range we want.

Original Code, gets last year's months to date (related to the current month).

ParallelPeriod([Date Appointment].[Calendar].[Year Number], 1, StrToMember("[Date Appointment].[Calendar].[Month In Year Number].[" + Format(Now(), "MMMM - yyyy") + "].Lag(Month(Now()) - 1)"))
:ParallelPeriod([Date Appointment].[Calendar].[Year Number], 1, StrToMember("[Date Appointment].[Calendar].[Month In Year Number].[" + Format(Now(), "MMMM - yyyy") + "].Lag(1)"))

In an MDX select query Returns this "on rows"

January - 2012 
February - 2012 
March - 2012 
April - 2012 
May - 2012 
June - 2012

Proposed code, bombs out with error message below.   I may need another way to do this (Exists?)

 Filter([Date Appointment].[Month In Year Number].Members
 , [Date Appointment].[Month In Year Number].Member_Value = [Date Appointment].[Months YTD Last Year Beg Rng].LastChild.MemberValue  )   --Run by itself returns January - 2012 
 :
 Filter([Date Appointment].[Month In Year Number].Members
 , [Date Appointment].[Month In Year Number].Member_Value = [Date Appointment].[Months YTD Last Year Beg Rng].LastChild.MemberValue  ) --Run by itself returns June - 2012 

Is there an alternative way to do this?

Thanks

Hello, 

I don't think you can replace Now() function dynamically generate current Year, Month or Date member with MDX. 

http://sqljoe.wordpress.com/2011/07/22/dynamically-generate-current-year-month-or-date-member-with-mdx/