Sql-server – MDX datetime comparison

mdxsql serverssas

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

Best Answer

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.

Related Question