I have a MDX calculated measure for rolling 30-day totals, and I want to mask the calculation for future members. I’m trying to use a CASE statement and the comparison does not work correctly. If the key of the Fiscal Month member is a datetime, shouldn’t this work?
CASE WHEN [Time].[Fiscal Month].CurrentMember < NOW() THEN 1 ELSE 0 END
An alternative, and sometimes better performing approach could be to add an attribute to your date dimension along the lines of “IsFuture”.
Depending on your data load/processing schedule you could have a field in your datawarehouse or a calculated column in your dsv precalculating if the date is in the future or not.
Instead of performing an on the fly calculation with an
IIF in your MDX you could then create a calculated measure using
SCOPE like this (untested, from memory)
Scope ( [Date].[IsFuture].& ) ; This = 1; end scope;
Just an idea I’m offering, your question doesn’t provide a lot of detail to create a working piece of sample code but this could be an alternative approach.
This would return empty if the date is in the future instead of 0 but depending on your requirements that could provide better results if your client is only returning
NOW() returns a DateTime, you cannot compare that to a Member. Dependending on the definition of the Time dimension you can convert the result of
NOW() to a member using MDX-Function
StrToMember, but there is no lower than on Members.
I use keys of the format “yyyyMMdd” for my time dimension. So that key is comparable like this:
WHEN [Time].[Time].CurrentMember.Properties("Key") < Format(Now(),"yyyyMMdd") THEN 1