MDX last day of week with tuples

So, working on a project with the TFS cube, a business requirement needed to retrieve the Last Day of the Week.

Now, in the TFS cube, there is a Date Dimension with two hierarchies already created out of the box.

The problem started when I was trying to use the LastChild function to get the last day of the week from the [Year – Week – Date] hierarchy. This function expects a Member of the hierarchy, and I needed to pass a tuple (or a set of members).

After doing some research and ask a couple of senior colleagues, we found the answer:

[Date].[Year - Week - Date Hierarchy].[Week]
[Date].[Year - Week - Date Hierarchy].CURRENTMEMBER

By using the Generate function we work with each member of a set separately, and then the function  joins the resulting sets by union. The Tail function is similar to LastChild, but Tail returns a subset from the end of a set and can be used to return the last member of the dimension. The Descendants function is needed to return the set of descendants of a member at a specified level or distance to memory.


Leave a Reply