Filter Measure by reference date column

0
votes

In my date dimension I have an attribute called CompareToDatekey which shows the date that should be used to compare measures through time. How can I build a calculated measure that will give the measure value for the compare date when I filter a particular primary date?

What I tried to do is to make a CompareTime dimension which has a referenced relation through the primary time dimension ([time dim].[CompareToDatekey] -> [TimeDim CompareDate].[datekey]) and then use the following calculated member :

WITH MEMBER [Measures].[CompareAmount]
AS 
(
    Sum(EXISTING [TimeDim CompareDate].[datekey].children
    ,[Measures].[Net Amount])
)

But the result is the measure filtered by the primary date and not the compare date.

Some test data :

datekey | ComparedToDatekey

20150109 | 20141219

20150110 | 20141220

datekey | NetAmount

20141219 | 1716648.85

20150110 | 2266777.12

20141220 | 2378598.98

20150109 | 1588123.89

I would like a calculated member which give the sum of net amount on 20141219:20141220 when I filter for 20150109:20150110

2
ssasmdx
Can you give some sample data for the Date Dimension with the two attributes (primary date and compare to date) and also the Fact table, including the Net Amount and the value of the primary date key? – The Dumb Radish

2 Answers

2
votes

Unfortunately, it's not an option to link dimensions, since filtering one of them doesn't mean filtering another. Their connection is used only for mapping, but not during slicing-dicing.

I see only boring StrToMember solution:

That's my test numbers: CompareDate_1Initial

Add property to the lowest level of Date dimension: CompareDate_2Property

Show this property as a measure: CompareDate_3PropertyInMDX

Finally, cover by StrToMember: CompareDate_4CompareAmount

It works, but I personally don't like such dynamic manipulations.

Sure thing, this solution doesn't require additional dimension, just a property. Your case script is smth like this:

with member [Measures].[CompareAmount]
as (StrToMember("[time dim].[datekey].&["
+[time dim].[datekey].Properties("Compare Date")
+"]"),[Measures].[Amount])
0
votes

How about this:

WITH MEMBER [Measures].[CompareAmount]
AS 
(
    SUM(
    EXISTS([TimeDim CompareDate].ComparedToDatekey.children, 
           EXISTING [TimeDim CompareDate].[datekey].children)
    ,[Measures].[Net Amount]
)