MDX To Compare The Currentmember Of The Time Dimension To Today

This is a cross-post from the Office PPS Planning site: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3379691&SiteID=17 I was hoping there may be some additional MDX resources here.

I'm trying to determine if the currentmember of the time dimension is > or < the value of today. I want to use this to change the the value of a member that I display on columns from actual (for prior to today) to forecast (after today). I have this MDX:


with

member [measures].[PYCY] as IIf(VBA!CInt(Format(VBA!Now(),"MM"))<10, VBA!CStr(VBA!CInt(VBA!Year(VBA!Now())-1)), VBA!CStr(VBA!CInt(VBA!Year(VBA!Now())+0)))

member [measures].[CYNY] as IIf(VBA!CInt(Format(VBA!Now(),"MM"))<10, VBA!CStr(VBA!CInt(VBA!Year(VBA!Now())+0)), VBA!CStr(VBA!CInt(VBA!Year(VBA!Now())+1)))

member [measures].[NYNY2] as IIf(VBA!CInt(Format(VBA!Now(),"MM"))<10, VBA!CStr(VBA!CInt(VBA!Year(VBA!Now())+1)), VBA!CStr(VBA!CInt(VBA!Year(VBA!Now())+2)))


member [measures].[test2] as Format(Now(), "yyyyMM")

member [measures].[test4] as [Time].[Day View].CurrentMember.Member_Key

member [measures].[diff] as DateDiff('m', Format(Now(), "yyyyMM"),[Time].[Day View].CurrentMember.Member_Key)


select

StrToSet('{' + Generate(Descendants(

{strtomember('[Time].[Day View].[year].&[' + [measures].[PYCY] + ']'), strtomember('[Time].[Day View].[year].&[' + [measures].[CYNY] + ']'), strtomember('[Time].[Day View].[year].&[' + [measures].[NYNY2] + ']') }

, [Time].[Day View].[Month]), [Time].[Day View].CurrentMember.UNIQUE_NAME, ", ") + '}')


* {[measures].[test2],[Measures].[test4],[measures].[diff]}



on 0,

[Scenario].[All Members].members

on 1

from [SalesForecasting]


Here is the result for 2 months:










October FY2007
October FY2007
October FY2007
November FY2007
November FY2007
November FY2007


test2
test4
diff
test2
test4
diff

All
200805
200610
#Error
200805
200611
#Error

None
200805
200610
#Error
200805
200611
#Error

Actual
200805
200610
#Error
200805
200611
#Error

Forecast
200805
200610
#Error
200805
200611
#Error

Override
200805
200610
#Error
200805
200611
#Error

Plan
200805
200610
#Error
200805
200611
#Error



The error is: #Error Execution of the managed stored procedure DateDiff failed with the following error: Exception has been thrown by the target of an invocation.Argument 'Date1' cannot be converted to type 'Date'..

Why can't DateDiff calculate the difference between 200805 & 200610?

Thanks