CODEWITHSUNDEEP
mdx
Mitch
Mitch

Reputation: 544

Dynamically select date

Goal is to pass prior year if current month is 1, else pass current year. I always want data through the prior month. I have dynamic month logic working, just the year is causing me issues. I am attempting this with an IIF statement but am consistently returning the current year. 

SELECT ( { IIF( Month(Now()) = '1', Strtomember(" [Posting Date].[Year].&[" + cstr(year(dateadd('yyyy',-1,now()))) + "] "),
                                                                 Strtomember(" [Posting Date].[Year].&[" + cstr(year(now())) + "] ")) 
                                                                 } ) ON COLUMNS

I have verified that each Strtomember piece works correctly independently when I remove the IIf. I am able to query current year and then swap the Strtomember and return current year -1.

Note: For testing purposes I have IIF( Month(Now()) = '6' as it is the current month at time of post. I should return current year -1, but am returning current year.

Upvotes: 1

Views: 229

Answers (1)

whytheq
whytheq

Reputation: 35557

Try changing the '1' to just a number 1:

Instead of Month(Now()) = '1'
Use Month(Now()) = 1

The above change works fine on my cube:

SELECT 
  {
    IIF
    (
      Month(Now()) = 6
     ,StrToMember
      ("[Date].[Date - Calendar Month].[Calendar Year].&["
          + 
            Cstr(Year(Dateadd('yyyy',-1,Now())))
        + "]"
      )
     ,StrToMember
      (
          "[Date].[Date - Calendar Month].[Calendar Year].&[" + Cstr(Year(Now()))
        + "] "
      )
    )
  } ON COLUMNS

Upvotes: 1

Related Questions