Searching A Dimension Using Value From Another Dimension

I recently have a requirement wherein, there are two time dimensions and I have to get a member in the first time dimension (T1), which is two months earlier than the member in the second time dimension (T2).

The column of the crosstab looks like this:

The first thing is to determine whether the two time dimensions have some common values. In this case, it was a good thing that the captions of members in both dimensions are the same even if their business keys are different.

So the next thing to do is to get the current member of T2 using well… currentMember function:

currentMember([Package].[T2].[Time Dim])

Next I get the caption of the current member of T2, in this case I used roleValue function (you can try using caption as well).:

roleValue(‘_memberCaption’;currentMember([Package].[T2].[Time Dim]))

Now that I have the caption of T2 member, I’ll filter T1 with the condition that the caption should be the same as T2:

filter([T1]; caption([T1]) = caption([T2])) or

filter([Package].[T1].[Forecasting Time].[Forecasting Month];roleValue(‘_memberCaption’;[Package].[T1].[Forecasting Time].[Forecasting Month]) =roleValue(‘_memberCaption’;currentMember([Package].[T2].[Time])))

Now you have the member in T2 which has the same caption as the current Member in T1… or in other words, the month in T2 which is the same month in T1.

However the filter functions returns a memberset. We need a member. But we do know that while the filter function returns a memberset, it will contain only 1 member. So we extract that member using the item function.

item([memberset], index)

or

item( filter([Package].[T1].[Forecasting Time].[Forecasting Month];roleValue(‘_memberCaption’;[Package].[T1].[Forecasting Time].[Forecasting Month]) = roleValue(‘_memberCaption’;currentMember([Package].[T2].[Time])));0)

Now I got the member, I want the member two months prior. I can use the lag function:

lag(item( filter([Package].[T1].[Forecasting Time].[Forecasting Month];roleValue(‘_memberCaption’;[Package].[T1].[Forecasting Time].[Forecasting Month]) = roleValue(‘_memberCaption’;currentMember([Package].[T2].[Time])));0);2)