MDX CurrentMamber.Lag - how to force hierarchy level used

How do I force the MDX CurrentMember.Lag(N)  method to operate at a particular level of the time hierarchy? I am creating a sales report that mixes weekly figures, monthly figures and yearly figures.  I would like to use the CurrentMember.Lag method to look back a given set of weeks or months for comparison purposes. I think the approach to take is to use is something like this (using AdventureWorks data):

Create member [Measures].[Month Ten Weeks Ago] AS
(
[Date].[Calendar].CurrentMember.Lag(10):[Date].[Calendar]CurrentMember.Lag(7),
[Measures].[Reseller Sales Amount]
)

Or

Create member [Measures].[The Months Ago] AS
(
[Date].[Calendar].CurrentMember.Lag(3),
[Measures].[Reseller Sales Amount]
)

But the report will be filtered for a particular date, so I suspect that these lag methods will be looking back in days rather than weeks or months. Is there a way to force them to use the Time hierarchy level of my choice?
LinksGu2Asked:
Who is Participating?
 
Tim HumphriesConnect With a Mentor DirectorCommented:
If CurrentMember is at your Date level you could use CurrentMember.Parent to get to the current week and Currentmember.Parent.Parent to get to the month (assuming Month->Week->Date hierarchy).

Also look at the Ancestor function which might be of use to you.

Tim
0
All Courses

From novice to tech pro — start learning today.