Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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].&[0]
) ;
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 NONEMPTY/NONEMPTYCROSSJOIN results.
The Function 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
