Search code examples
ssasmdx

IIF in With Member comparing dates

I am very new to MDX and have a basic question. I would like a calculated measure which returns one value for dates less than a specified date, and another value if greater than a specified date. I have attempted:

with member [measures].[tempmeasure] as 
    IIF
       (
        [Date].[Date].CurrentMember < [Date].[Date].&[20100501]
        , 1
        , 2
       )

select 
   [Date].[Date].Members
   * 
   [measures].[tempmeasure] on columns
from [MyCube]

And this does not seem to work. That is using MS SSAS 2008. Any ideas what I could be doing wrong?

Solution

  • Please use below expression 

    IIF
   (
    [Date].[Date].Currentmember.MemberValue  < 
    [Date].[Date].&[20100501].MemberValue
    , 1
    , 2
   )

    I have tested it is working as expected.....

    Please let me know if you have any questions