MDX ParallelPeriod Function

In Multidimensional Expression, ParallelPeriod Function is used to return member from a prior period in the same relative position as a specified member. For example, If you want to navigate to particular member then we can use this ParallelPeriod function.

MDX ParallelPeriod Function Syntax

The basic syntax of the MDX ParallelPeriod Function is:

1	PARALLELPERIOD( Level_Expression, Member_Position, Member_Expression)

Member_Expression: Any Multidimensional Expression that returns valid Member.

Level_Expression: Please specify the level you want to navigate

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

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

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

MDX ParallelPeriod Function with Zero

In this example we will show you, What happen when we use Zero value for the ParallelPeriod Function. The following query will return the Internet Sales amount of December in the Calender Year 2013 itself.

CODE

1 2 3 4 5 6 7 8

SELECT   [Measures].[Internet Sales Amount] ON COLUMNS,   PARALLELPERIOD (             [Date].[Calendar].[Month],             0,     [Date].[Calendar].[Month].[December 2013]         ) ON ROWS FROM [Adventure Works]

OUTPUT

MDX ParallelPeriod Function with Positive Value

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

CODE

1 2 3 4 5 6 7 8

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

OUTPUT

MDX ParallelPeriod Function with Negative Value

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

CODE

1 2 3 4 5 6 7 8

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

OUTPUT

Finding Range Using MDX ParallelPeriod Function

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

CODE

1 2 3 4 5 6 7 8 9 10

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

OUTPUT

Thank You for Visiting Our Blog