MDX PrevMember Function

In Multidimensional Expression, PrevMember function will return the same level member at the Previous position.

MDX Prevmember Function Syntax

The basic syntax of the MDX Prevmember is:

1	Member_Expression.PREVMEMBER

Member_Expression: Any Multidimensional Expression that returns valid Member.

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

MDX Prevmember Function Example

In this example we will show you, How to write the PrevMember function in Multidimensional Expression query. The following query will return the Reseller Sales amount of the Calender Year 2012 because [CY 2013].PrevMember will look for year before 2013, which is 2012.

CODE

1 2 3

SELECT [Date].[Calendar].[Calendar Year].[CY 2013].PREVMEMBER ON COLUMNS,        [Measures].[Reseller Sales Amount] ON ROWS FROM [Adventure Works]

OUTPUT

MDX Prevmember Function Example 2

In this example we will show you, How to write the multiple PrevMember function in single Multidimensional Expression query. The following query will return the Reseller Sales amount of the Calender Year 2011.

CODE

1 2 3

SELECT [Date].[Calendar].[Calendar Year].[CY 2013].PREVMEMBER.PREVMEMBER ON 0       ,[Measures].[Reseller Sales Amount] ON ROWS FROM [Adventure Works]

OUTPUT

Analysis:

First, [CY 2013].PrevMember will look for the year before 2013, which is 2012

1	[Date].[Calendar].[Calendar Year].[CY 2013].PREVMEMBER

Second, [CY 2011].PrevMember.PrevMember will look for the year before 2012, which is 2011

1	[Date].[Calendar].[Calendar Year].[CY 2013].PREVMEMBER.PREVMEMBER

Finding Range Using MDX Prevmember Function

In this example we will show you, How to find the Range using Prevmember function. The following query will return the Reseller Sales amount from Calender Year 2011 to 2013 because we used the range symbol (:) between

[CY 2013] and [CY 2013].PrevMember.PrevMember

CODE

1 2 3 4 5

SELECT   [Date].[Calendar].[Calendar Year].[CY 2013]:   [Date].[Calendar].[Calendar Year].[CY 2013].PREVMEMBER.PREVMEMBER ON 0 ,[Measures].[Reseller Sales Amount] ON ROWS FROM [Adventure Works]

OUTPUT

MDX Prevmember Function Alternative 1

In this example we will show you, We are going to use Lead Function with Negative integer value. Please refer MDX Lead Function article to understand the function

CODE

1 2 3 4 5

SELECT   [Date].[Calendar].[Calendar Year].[CY 2013]:   [Date].[Calendar].[Calendar Year].[CY 2013].LEAD(-2) ON 0 ,[Measures].[Reseller Sales Amount] ON ROWS FROM [Adventure Works]

OUTPUT

MDX Prevmember Function Alternative 2

In this example we will show you, We are going to use Lag Function with Positive integer value. Please refer MDX Lag Function article to understand the function

CODE

1 2 3 4 5

SELECT   [Date].[Calendar].[Calendar Year].[CY 2013]:   [Date].[Calendar].[Calendar Year].[CY 2013].LAG(2) ON 0 ,[Measures].[Reseller Sales Amount] ON ROWS FROM [Adventure Works]

OUTPUT

Thank You for Visiting Our Blog