MDX Lead Function

In Multidimensional Expression, Lead 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 LEAD function.


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


The basic syntax of the MDX Lead is:

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


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

MDX Lead Function with Zero

In this example we will show you, What happen when we use Zero value for the Lead 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].LEAD(0) ON ROWS
FROM [Adventure Works]


OUTPUT

MDX Lead Function with Positive Value

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


CODE


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


OUTPUT

MDX Lead Function with Negative Value

In this example we will show you, What happen when we use Negative integer value for the Lead Function. The following query will return the Reseller Sales amount and Tax amount of the Calender Year 2010 because Lead function with negative 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].LEAD(-3) ON ROWS
FROM [Adventure Works]


OUTPUT

Finding Range Using MDX Lead Function

In this example we will show you, How to find the Range using Lead 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 2010] and [CY 2010].LEAD (3).


CODE


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

OUTPUT





If you want to find the range by moving backwards then use following MDX query


CODE


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