CODEWITHSUNDEEP
attributesmdxdimension
ilija veselica
ilija veselica

Reputation: 9574

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] )

Upvotes: 0

Views: 73

Answers (2)

whytheq
whytheq

Reputation: 35605

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  ;

Upvotes: 1

nsousa
nsousa

Reputation: 4544

.CurrentMember is evaluated at the row level, doesn't look into the slicer. The slicer is a global restriction on the cube, providing a sub-cube domain for your query.

In your query, [Date].[Fiscal].CurrentMember is underfined, as there is nothing on the Rows clause.

Try

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

Upvotes: 0

Related Questions