MDX LastPeriods Function

The MDX LastPeriods function used to navigate between same level members. It returns the set of members up to the specified position, including the given number itself. For example, If you want to list the members from one position to a particular member, then we can use this MDX LastPeriods function.

TIP: If you know the index position of your destination member, you can use this MDX LastPeriod function.

MDX LastPeriods Function Syntax

The basic syntax of the LastPeriods in Multidimensional Expression is as shown below:

LASTPERIODS (Member_Position, Member_Expression)

Member_Expression: Any Multidimensional Expression that returns valid Member.

Member_Position: Please specify the position of a member you want to Navigate.

  • If we use Zero as the Member_Position, the MDX LastPeriods Function will return the empty set.
  • If we use Negative Value, the LastPeriods Function will move forward to the specified value and returns the set of members up to that position, including the Member_Expression.
  • And If we use Positive Value, the LastPeriods Function will move Backwards to a specified value and returns the set of members up to that position, including the Member_Expression.

The MDX LastPeriods Function is used to navigate between the same Members level. For example, If we mentioned City member as Member_Expression, the LastPeriods function will navigate between remaining Cities. It will not go Level Up (State – Province) or Level down (Postal Code).

How to write MDX LastPeriods function to navigate both forward and backward with examples?. For this, we are going to use the below-shown data.

MDX LASTPERIODS FUNCTION

MDX LastPeriods Function with Zero

In this example, we will show you, What happens when we use Zero value for the LastPeriods Function. The following MDX query will return the Empty set

SELECT 
  [Measures].[Internet Sales Amount] ON COLUMNS,
  LASTPERIODS (
               0, 
              [Date].[Calendar].[Calendar Year].[CY 2013] 
	       ) ON ROWS
FROM [Adventure Works]

MDX LASTPERIODS FUNCTION 1

MDX LastPeriods Function with Positive Value

What happens when we use Positive integer value in the LastPeriods Function?. The following query will return the Internet Sales amount of the Calendar Year 2013, 2012, and 2011 because LastPeriods function with Positive value (3) will move back 2 years + year 2013. It is because the LastPeriods function includes 2013 itself.

SELECT 
  [Measures].[Internet Sales Amount] ON COLUMNS,
  LASTPERIODS (
               3, 
              [Date].[Calendar].[Calendar Year].[CY 2013] 
	       ) ON ROWS
FROM [Adventure Works]

MDX LASTPERIODS FUNCTION 2

MDX LastPeriods Function with Negative Value

In this example, we show you, What happens when we use Negative integer value for the LastPeriods Function. The following query will return the Internet Sales amount of the Calendar Year 2010, 2011, 2012, and 2013 because LastPeriods function with Negative value (-4) will move forward 3 years + year 2010. It is because the LastPeriods function includes 2010 itself.

SELECT 
  [Measures].[Internet Sales Amount] ON COLUMNS,
  LASTPERIODS (
              -4, 
             [Date].[Calendar].[Calendar Year].[CY 2010] 
	      ) ON ROWS
FROM [Adventure Works]

MDX LASTPERIODS FUNCTION 3

Categories MDX