MDX Lag Function

In Multidimensional Expression, Lag function is used to navigate between same level members and returns the members at the specified position. For example, If you want to navigate to particular member then we can use this Lag function.


TIP: In MDX, Lead Function with Positive Value is Equal to Lag with Negative value.
MDX Lag Function Syntax


The basic syntax of the MDX Lag is:

Member_Expression.LAG (Member_Position)

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 Lag Function will write the same Member_Expression that we mentioned before Lag function.
If we use Negative Value as the Member_Position then Lag Function will move forward to specified value and returns the Member_Expression at that position.
If we use Positive Value as the Member_Position then Lag Function will move Backwards to specified value and returns the Member_Expression at that position.


NOTE: Lag Function is used to navigate between same Members level. For example, If we mentioned City member as Member_Expression then Lag 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 Lag function to navigate both forward and backward with examples. For this, we are going to use below shown data.

MDX Lag Function with Zero

In this example we will show you, What happen when we use Zero value for the Lag Function. The following query will return the Reseller Sales amount and Tax amount of the Calender Year 2013 itself.


CODE


SELECT {
[Measures].[Reseller Sales Amount]
,[Measures].[Reseller Tax Amount] } ON COLUMNS,
[Date].[Calendar].[Calendar Year].[CY 2013].LAG(0) ON ROWS
FROM [Adventure Works]


OUTPUT

MDX Lag Function with Positive Value

In this example we will show you, What happen when we use Positive integer value in Lag Function. The following query will return the Reseller Sales amount and Tax amount of the Calender Year 2010 because Lag function with Positive value (3) will move back 3 years from the given year (2013 – 3 = 2010)


CODE

SELECT {
[Measures].[Reseller Sales Amount]
,[Measures].[Reseller Tax Amount] } ON COLUMNS,
[Date].[Calendar].[Calendar Year].[CY 2013].LAG(3) ON ROWS
FROM [Adventure Works]


OUTPUT

MDX Lag Function with Negative Value

In this example we will show you, What happen when we use Negative integer value for the Lag Function. The following query will return the Reseller Sales amount and Tax amount of the Calender Year 2013 because Lag function with Negative value (-2) will move forward 2 years from the given year (2011 + 2 = 2013)


CODE


SELECT {
[Measures].[Reseller Sales Amount]
,[Measures].[Reseller Tax Amount] } ON COLUMNS,
[Date].[Calendar].[Calendar Year].[CY 2011].LAG(-2) ON ROWS
FROM [Adventure Works]


OUTPUT

Finding Range Using MDX Lag Function

In this example we will show you, How to find the Range using Lag function. The following query will return the Reseller Sales amount and Tax amount from Calender Year 2010 to 2013 because we used the range symbol (:) between [CY 2013] and [CY 2013].LAG (3).


CODE


SELECT {
[Measures].[Reseller Sales Amount]
,[Measures].[Reseller Tax Amount] } ON COLUMNS,
[Date].[Calendar].[Calendar Year].[CY 2013]:
[Date].[Calendar].[Calendar Year].[CY 2013].LAG(3) ON ROWS
FROM [Adventure Works]


OUTPUT





If you want to find the range by moving forward then use Lag function with negative value.