Search code examples
attributesmdxdimension

MDX return fiscal PriorMTD date as value


I am trying to create MDX Calculated member which returns prior mtd date.

This is Calculated member I've created:

CREATE MEMBER CURRENTCUBE.[Measures].PriorMTDDate
 AS cousin(
            [Date].[Fiscal].CurrentMember,
            [Date].[Fiscal].CurrentMember.parent.parent.lag(1)
        ), 
VISIBLE = 1  ;   

And this is query, but it returns just null:

select {[Measures].[PriorMTDDate]} on 0
from [WH_Cube]
WHERE ( [Date].[Fiscal].[Date].&[2014-09-12T00:00:00] )

Any idea what am I doing wrong?

EDIT: Another example returning null:

WITH MEMBER Measures.x AS  
    [Date].[Fiscal].CurrentMember 
SELECT Measures.x ON 0
FROM [WH_Cube]
WHERE ( [Date].[Fiscal].[Date].&[2014-09-30T00:00:00] )

Solution

  • Does a measure need to be a numeric value?:

    CREATE MEMBER CURRENTCUBE.[Measures].PriorMTDDate
     AS cousin(
                [Date].[Fiscal].CurrentMember,
                [Date].[Fiscal].CurrentMember.parent.parent.lag(1)
            ).MemberValue , 
    VISIBLE = 1  ;