• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2448
  • Last Modified:

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?
0
LinksGu2
Asked:
LinksGu2
1 Solution
 
Tim HumphriesDirectorCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now