MDX ParallelPeriod Function

The MDX ParallelPeriod Function 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 a particular member, then we can use this MDX ParallelPeriod function.

MDX ParallelPeriod Function Syntax

The basic syntax of the MDX ParallelPeriod Function in Multidimensional Expression is as shown below:

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

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

MDX PARALLELPERIOD FUNCTION

MDX ParallelPeriod Function with Zero

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

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

MDX ParallelPeriod Function with Positive Value

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

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

ParallelPeriod Function with Negative Value

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

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

Range Using ParallelPeriod Function

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

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]
MDX PARALLELPERIOD FUNCTION 4
Categories MDX