SSAS MDX - Select from one dimension depending on two other dimensions

Question

I have three dimensions, a [Transaction], [Trade Date] and [Report Date]. The [Transaction] is related to both, and I would like to define a function for a KPI in which I look for the following:

For each date where there exists a trade in as a transaction, check from this date and a range that goes 30 days back for transactions where the difference between [Trade Date] and [Report Date] is greater than 1.
I want to sum up the [Measure].[N of Transactions] in this range, for each trader on each date.

I've tried the following:

WITH 
    SET [Filtered] AS FILTER(NONEMPTY({[Trade Date].[Calendar].CurrentMember.lag(30):[Trade Date].[Calendar].CurrentMember}), 
                                DateDiff("d", 
                                    CDate([Report Date].[Calendar].CurrentMember.MemberValue), 
                                    CDate([Trade Date].[Calendar].CurrentMember.Lag(1).MemberValue)
                                ) > 1
                            )
    MEMBER [Measures].[x] AS SUM([Filtered].CURRENTMEMBER, [Measures].[N of Transactions])
SELECT [Measures].[x] on 0,
[Transaction].[Trader].&[some_id_here] on 1
FROM [Relevant]
WHERE [Trade Date].[Calendar].[2014-02-16]

But this obviously doesn't work.

                        +----------------+                        
                        |factTransaction |                        
                        |                |                        
                   +----+                +----+                   
+-------------+    |    |                |    |    +-------------+
|dimTradeDate |    |    |                |    |    |dimReportDate|
|             |    |    |                |    |    |             |
|             +-----    |                |    +----+             |
|             |         |                |         |             |
|             |         |                |         |             |
|             |         |                |         |             |
|             |         |                |         |             |
+-------------+         +----------------+         +-------------+

Above is the structure in the designer. To sum it up, how do I write an MDX query that looks at each date, backtracks 30 days and creates a range. From this, look at all transactions and return the sum of the measure [Measures].[N of Transactions] for that range and [Transaction].[Trader]?

+1 nicely structured question. One question though: in your script you only seem to have one date - `[Trade Date].[Calendar].[2014-02-16]` specified in the `WHERE` clause? Also have you downloaded the Adv Wks cube? ... it is really useful to translate questions into scripts against that cube as others may also have access to that cube. — whytheq, Jul 09 '14 at 09:42
I'll recheck the `WHERE`-clause and download the AW-cube to see if I can make it even more clear and point out my problems! — Daniel B, Jul 09 '14 at 10:25

score 0 · Answer 1 · edited May 23 '17 at 11:57

I had a somehow similar question today, and got answered. See if it somehow helps you:

Mdx Using a member property as an argument for a strtomember

Might be the same issue... the set condition is evaluated before the rows definition, so, when you specify currentmember on the Set, it doesn't get the desired argument. I've solved my issue putting the set inside the member definition, and, as I had seen your question before, thought that it might be a similar case ...

CF.

SSAS MDX - Select from one dimension depending on two other dimensions

1 Answers1