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:
GENERATE( [Date].[Year - Week - Date Hierarchy].[Week] , TAIL( DESCENDANTS( [Date].[Year - Week - Date Hierarchy].CURRENTMEMBER ),1))
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.