For a report, I needed to find out when employee X last registered an hour.
My measure is called Total Hours (hours can be billable or non billable, but the Total hours will calculate both types, and both types are valid in this report.)
My Date dimension is built on Years - Months - Days.
I tried to use Tail, and filter, and order and topcount, but couldn't figure out how to do it.
Once again a suggestion from Deepak Puri helped me figure it out:
With Member [Measures].[DateName] as
'[Date].CurrentMember.UniqueName'
select {[Measures].[DateName]} on columns
from [MyCube]
where (Tail(NonEmptyCrossJoin([Date].[Day].Members,
{[Employee].&[1005]},
{[Measures].[Total Hours]}, 1)).Item(0))
In the end I added one more calculated member, displaying the value of [Date].CurrentMember.Properties("Key"). In my Date dimension, this gives me the name of the date as 20051108 (November 8, 2005), instead of [Date].[All Date].[2005].[200511].[08], which is the Unique Name of the last cell for the non empty combination of Employee 1005, Dates and Total Hours.