MDX

[NotSQL]

hi I wondering if anyone can help.

I know the following syntax works,

SELECT ([Measures].[SALES]) on columns,
LastPeriods(3,[Date].[Hierarchy].[Year].&[2008].&[WK02]) ON Rows
FROM [TestCube]

however instead of hard coding the week part i want it to pick up the last member in the hierarchy.

Eg

SELECT ([Measures].[SALES]) on columns,
LastPeriods(3,[Date].[Hierarchy].[Year].[LastMemberWeek]) ON Rows
FROM [TestCube]

Hope someone can help

 

[eo]

Have you tried the

Tail (Filter (...

functions

The Filter function should identify all the hierarchy members (in your case the week) with data, and the Tail will select the last one

EO
Hertfordshire, England

 

[MDXer]

Untested but should work.

SELECT ([Measures].[SALES]) on columns,
LastPeriods(3,[Date].[Hierarchy].[Year].LastChild.LastChild) ON Rows
FROM [TestCube]

Remember when you post MDX code that .&[] is a unique Identifier and sometimes can make it difficult for someone else to deduce the structure of a dimension.