MDX LastPeriods Function

In Multidimensional Expression, LastPeriods function is used to navigate between same level members and 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 particular member then we can use this LastPeriods function.

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

The basic syntax of the MDX LastPeriods is:


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 then LastPeriods Function will return the empty set.
If we use Negative Value as the Member_Position then LastPeriods Function will move forward to specified value and returns the set of members up to that position including the Member_Expression.
If we use Positive Value as the Member_Position then LastPeriods Function will move Backwards to specified value and returns the set of members up to that position including the Member_Expression.


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


In this article we will show you, How to write LastPeriods function to navigate both forward and backward with examples. For this, we are going to use below shown data.

MDX LastPeriods Function with Zero

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


CODE


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


OUTPUT

MDX LastPeriods Function with Positive Value

In this example we will show you, What happen when we use Positive integer value in LastPeriods Function. The following query will return the Internet Sales amount of the Calender Year 2013, 2012 and 2011 because LastPeriods function with Positive value (3) will move back 2 years + year 2013. This is because LastPeriods function includes 2013 itself.


CODE


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


OUTPUT

MDX LastPeriods Function with Negative Value

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


CODE


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


OUTPUT