Filtering a Dimension Relative to a CurrentMember in MDX

Question

I'm having a bit of trouble accomplishing something that I think should be relatively straightforward in MDX. I would like to create a calculated member that provides a sum of one of my measures over the previous two weeks at a given point in time. My time dimension looks like:

TimeId    TradingDate   Day of Week
-----------------------------------
1000      11/1/2012     Thursday
1001      11/2/2012     Friday
1002      11/5/2012     Monday
1003      11/6/2012     Tuesday
...       ...

What makes this particularly difficult is that my Time dimension is not quite complete. The members of my Time dimension only correspond to trading days in the stock market, and not all time. This means that weekends, holidays, or any other day in which the stock market is closed are excluded. This also means the normal methods of traversing time such as LAG or PARALLELPERIOD will not work quite right here. LAG(14), for example, means "14 trading days", which at any given point could represent a variable length of actual time.

Inside my calculated member, I'm attempting to use FILTER in order to get only time members that are within the previous two weeks of the CurrentMember. However, I can't seem to figure out the proper syntax (if there is one) to accomplish this. I imagine it would be something like:

WITH MEMBER [Sum of Price Previous 2 Weeks] AS
    SUM(
        FILTER(
            [Time].[TimeId].Children
            , [Time].[TradingDate].MemberValue 
              >= VBA!DATEADD("ww", -2, [Time].[TradingDate].CurrentMember.MemberValue)
        )
        , [Price]
    )

However, this doesn't quite work. I can't seem to separate the context of the calculated members current iteration from what would be a separate context inside of the FILTER function. In other words, I'm not sure how to say:

"When iterating over the set inside of FILTER, compare the current member of each iteration against the value of the CurrentMember in the scope of the calculated member"

Is what I'm trying to accomplish even possible? Is there a different approach I could be taking to accomplish what I'm after?

Answer 1

The result you'll get from a calculated member will depend on the axis of your query. So first, make sure you have [Time].[TradingDate] in your axis.

Second, your [Time].[TradingDate] hierarchy should be ordered by Key (I assume TradingDate is the key).

Now you can use this member definition:

WITH MEMBER [Sum of Price Previous 2 Weeks] AS
    SUM(
        [Time].[TradingDate].CurrentMember.Lag(14):[Time].[TradingDate].CurrentMember, [Price]
    )