business intelligence consulting
 

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

by Hans Esquivel 24. January 2013 08:34

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;;-"

SELECT

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 !

SQL Server | Analysis Services | Multidimensional Expressions (MDX)

About Us

InfoToad Consulting provides Data Management and Business Intelligence Solutions and Services. If you would like to learn more about our services or solutons, please visit us at http://www.infotoad.com or call us at (877) 488-0566.

Month List

Tag cloud