MDX: Cousin Function

I recently found a need to reference an alternate hiearchay in an MDX member formula in an ASO cube.

My first attempt was to utilize the Lag function as I had in many other formuals, but found that I kept getting results of zero.  Using the MDX Script Editor in EAS, I found that the Lag function was only valid within the hiearchy that the specified member was found in.  After reviewing the list of available functions, I took a look at the COUSIN function.  I found it was exactly what I needed. 

Here is an example of how it might be utilized:

Let's assume fort his example that you have two hiearchies within your period dimension.  Let's also assume that data is loaded on a weekly basis.  The first hiearchy provides your weeks, months, quarter, and year.  Your alternate hiearchy is a dynamic calc of the quarter to date as of each week.  So your two hiearchies look like this:

For each member you could do a unique formula, specific to each week:

• WK1 = [Wk1]
• WK2 = Sum(MemberRange([WK1],[WK2])
• WK3 = Sum(MemberRange([WK1],[WK3])
• WK4 = Sum(MemberRange([WK1],[WK4])
• WK5 = Sum(MemberRange([WK1],[WK5])

Or instead you could use the the same formula for each WK QTD member of a quarter:

• WK1 thru WK13 = Sum(MemberRange([WK1],Cousin([Period].CurrentMember,[Year])))
• WK14 thru WK26 = Sum(MemberRange([WK14],Cousin([Period].CurrentMember,[Year])))
• WK27 thru WK39 = Sum(MemberRange([WK27],Cousin([Period].CurrentMember,[Year])))
• WK40 thru WK52 = Sum(MemberRange([WK40],Cousin([Period].CurrentMember,[Year])))

It could be taken the next step so that the same formual is use for each WK QTD member:

• Sum(MemberRange([Period].CurrentMember.Parent.Parent.FirstChild.FirstChild,Cousin([Period].CurrentMember,[Year])))

Please note there are probably several different ways that you can set your the first member of your member range to by dynamic.  This is probably a rather crude way, but since this post is about the COUSIN function, I went with the simplest route that would validate.

In summary, the COUSIN function allows the flexibility reference a child member at the same relative position as a member from another ancestor.  This can be used within the same member hiearchy or across another member hiearchy.