Calculate Growth from Prior Period values, using the ParallelPeriod (MDX) function

In this session I'll show you how to use the ParallelPeriod function in MDX to query the change in growth between two periods (e.g., last year and current year). This function is really useful while navigating through different periods such as year, month, quarter and semester. For example, this could be useful in providing business analysts with growth trend analysis for products and/or services.

What is ParallelPeriod (MDX) function?

The ParallelPeriod returns a member from a prior period in the same relative position as a specified member. The Function takes 3 Arguments:

  1. Level_Expression - A valid Multidimensional Expressions (MDX) expression that returns a level.
  2. Index - A valid numeric expression that specifies the number of parallel periods to lag.
  3. Member_Expression - A valid Multidimensional Expressions (MDX) expression that returns a member.


Previous Year Growth Example 

WITH MEMBER [Measures].[Period Growth] AS

([Date].[Calendar].CurrentMember, [Measures].[Reseller Sales Amount]) -

(ParallelPeriod([Date].[Calendar].[Calendar Year],1,[Date].[Calendar].CurrentMember),

[Measures].[Reseller Sales Amount]), FORMAT_STRING="$#,#.00;;-"


NON EMPTY [Date].[Calendar].[Calendar Year] ON 0,

 {[Product].[Product Categories].[Category]}*

{[Measures].[Reseller Sales Amount],[Measures].[Period Growth]} ON 1

FROM [Adventure Works]

The example above uses the ParallelPeriod function to get the "Reseller Sales Amount" value from the previous Calendar Year and subtracts that from current year "Reseller Sales Amount".


Reference: ParallelPeriod (MDX)


Cheers !

Add comment