MDX to Determine Week Ending Date

Hello,

I'm trying to write MDX that will return the last child for the week having a full 7 days of data. I'm close, but can't figure out how to get the date to return. The sample below returns the correct week, but whenever I use "lastchild", etc. I get that lastchild expects a member and a tuple is being used.

WithMember measures.X asCount(([Date Dimension].[Time - Weeks].CurrentMember.children,[Measures].[Total Dollars]) , EXCLUDEEMPTY )

Select

Tail(Filter({[Date Dimension].[Time - Weeks].[Week].&[2007]&[18]:[Date Dimension].[Time - Weeks].[Week].&[2007]&[26]}, measures.X.Value = 7 )) on 0,

nullon 1

From

[MyCube]

This is to be used in a report header, so ultimately I'd like it to return a string like "Data as of Week Ending 6/16/2007"

Any help is appreciated.

So, this doesn't answer the question, but could you approach the overall problem another way to get what you're looking for? For example, you could have an attribute in your date dimension that recorded the day of the week number from 1 to 7. If you assume that if there exists data for the 7th day, the week is completed. If this gives you what you're looking for, you could determine the last day 7 associated with fact data with a query like this:

Code Snippet

select

{} on 0,

TAIL(

HIERARCHIZE(

EXISTS(

[Delivery Date].[Date].Members,

[Delivery Date].[Day of Week].&[7],

'Internet Sales'

)

),

1

) on 1

from [Adventure Works]

|||

I was actually able to get around the issue by using .item(0). See the working code below:

WithMember measures.X asCount(([Date Dimension].[Time - Weeks].CurrentMember.children,[Measures].[Total Dollars]) , EXCLUDEEMPTY )

Select

nullon 0,

Tail(Tail(Filter({[Date Dimension].[Time - Weeks].[Week].&[2007]&[1]:[Date Dimension].[Time - Weeks].[Week].&[2007]&[53]}, measures.X.Value = 7 )).Item(0).Children) on 1

From

[MyCube]

This code returns the date of the last day of the last full week of data.