2

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  
3

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 approach that seems to work best is having a PeriodOrdinal attribute in my time dimension where the current period is '0' and other periods are positive or negative integers. I have an existing business process to shift the period at closing, so the time dimension gets re-processed then anyway. – Jeff Sacksteder Jun 23 '17 at 15:07
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.