CODEWITHSUNDEEP
ssasmdx
whytheq
whytheq

Reputation: 35557

Move back x members in same hierarchy

Is there a function which moves back a set number of members in a specified hierarchy's level?

It would be something like the following if I want to move back two members:

SOMEFUNCTION([someMember], [specifiedLevel],-2)

So an example would be:

WITH MEMBER [Date].[Date - Calendar Month].[2MthPrev] AS
    SOMEFUNCTION(
    CLOSINGPERIOD([Date].[Date - Calendar Month].[Calendar Month]),
    [Date].[Date - Calendar Month].[Calendar Month],
    -2
    )

Upvotes: 1

Views: 84

Answers (1)

kzhen
kzhen

Reputation: 3118

There are a few different functions that you could try out:

Lag: Returns the member that is a specified number of positions before a specified member at the member's level.

Example: this will return December 2001

SELECT [Date].[Fiscal].[Month].[February 2002].Lag(2) ON 0
FROM [Adventure Works]

See http://technet.microsoft.com/en-us/library/ms144866.aspx for more details

ParallelPeriod: Returns a member from a prior period in the same relative position as a specified member.

Example:

SELECT ParallelPeriod ([Date].[Calendar].[Calendar Quarter], 3, 
       [Date].[Calendar].[Month].[October 2003]) ON 0
FROM [Adventure Works]

See http://technet.microsoft.com/en-us/library/ms145500.aspx for more details

(Examples are taken from the technet site)

Upvotes: 2

Related Questions