Search code examples
ssasmdx

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
    )
Solution

  • 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)