SSAS MDX Problem with IIF and TAIL() function

Question

Experts,

I am using SQL SSAS Std. 2017 and in the end want to create a calculated member that returns either the last month's day of my data or the current day if the last existing data is of today. (=> If today is Aug-31 I want to retrieve Aug-31 of my data, otherwise if today is e.g. Aug-30 retrieve Jul-31)

To develop this member I am currently in the process of creating a MDX query in SQL Server. I am having difficulties to understand what is actually a "tuple set expression" (because the TAIL() function shall return a subset (ergo set) according to MSDN) but in fact I am receiving errors when playing around with the .Item(0) function on its result. In MSDN I cannot find information about "tuple sets" and how to make them do, what I want.

My Date Dimension has a Hierarchy JMT (Year | Month | Day | Date Key of type DATE).

To receive the most current date member of the cross product I am using the TAIL(NONEMPTY(Date...Members, { (DimX.&.. , DimY.&.. , DimZ.&..) })) expression which works fine.

But how do I choose between today's or the previous month's date?

My MDX for testing purposes on February (2) is as follows:

        SELECT { 
        IIF(
            TAIL(NONEMPTY([DateDim].[JMT].[T].Members, { ([DimX].[X].&[200], [DimY].[Company].&[4499166], [DateDim].[JMT].[M].&[2020]&[2]) })).Item(0) --.Properties('Date Key', TYPED)
            > NOW()
            , TAIL(NONEMPTY([DateDim].[JMT].[T].Members, { ([DimX].[X].&[200], [DimY].[Company].&[4499166], [DateDim].[JMT].[M].&[2020]&[1]) }))
            , TAIL(NONEMPTY([DateDim].[JMT].[T].Members, { ([DimX].[X].&[200], [DimY].[Company].&[4499166], [DateDim].[JMT].[M].&[2020]&[2]) }))
            )
--      , 
--      TAIL(NONEMPTY([DateDim].[JMT].[T].Members, { ([DimX].[X].&[200], [DimY].[Company].&[4499166], [DateDim].[JMT].[M].&[2020]&[2]) })) } on columns  
    , { [Measures].[Turnover] } on rows
    FROM [Finance]

Result:

As you can see, the IIF function does not do what I want. It assumes .Item(0) is greater than NOW() therefore returning the "31" member of January (1). Expected: "29" of February.

I guess, it might be a problem with the data types and the actual value returned by .Item(0). But if I want to use the .Properties('Date Key', TYPED) it states "The Date Key Dimension Attribute could not be found. See below picture.

In the image of the DateDim it should be "DateDim.JMT" in the blue area ;-).

Do you have any suggestions? Thank you, Cordt

whytheq whytheq · Accepted Answer · 2020-08-21T16:38:00

If you switch this:

TAIL(NONEMPTY([DateDim].[JMT].[T].Members, { ([DimX].[X].&[200], [DimY].[Company].&[4499166], [DateDim].[JMT].[M].&[2020]&[2]) })).Item(0)

to the following does it help?

Tail
(
  NonEmpty
  (
    [DateDim].[JMT].[T].MEMBERS
   ,{
      (
        [DimX].[X].&[200]
       ,[DimY].[Company].&[4499166]
       ,[DateDim].[JMT].[M].&[2020]&[2]
      )
    }
  )
).Item(0).Item(0).MemberValue

SSAS MDX Problem with IIF and TAIL() function

1 Answers