Project Botticelli

MDX: Previous Period Growths, Shares & Moving Averages Purchase this course

24 February 2014 · 2946 views

Relative Tuple & Set Patterns

Chris Webb discusses Previous Period Growth MDX Pattern

In this 40-minute video by Chris Webb you will see how common business calculations can be implemented in MDX.

There are two popular types of calculations in MDX. The first is the relative tuple pattern. In this pattern the CurrentMember function is used to return a member from a hierarchy; this member is then passed to another function such as PrevMember to find another member relative to that member. Subsequently, this new member is then used inside a dynamic tuple expression to return a value from the cube. Frequently used examples of this type of calculation are: previous period growth, and percentage share calculations.

To help with formatting, values returned by calculated members can have a number format applied to them with the Format_String property. Division-by-zero (or division-by-null) errors can be avoided either by using an IIF() to check the value you are dividing by, or, if you are using SSAS 2012 or newer, the Divide() function.

The second common calculation pattern is the relative set pattern. In this pattern the starting point is still the CurrentMember function, but this time it is used to construct a set expression which is then passed to a numeric function such as Sum() or Count(). A three-period moving average calculation is a good example of this pattern.

Watch this video to learn about these common calculations, and do not miss the next video in our MDX series, “More Member and Set Functions,” which covers other useful business calculation patterns.

Log in or purchase access below to the premium version of this content.

Purchase This Course or Full Access Subscription

Single Course

$250/once

Access this course for its lifetime*.
Purchase
Subscription Best Value

$480/year

Access all content on this site for 1 year.
Purchase
Group Purchase

from $480/year

For small business & enterprise.
Group Purchase

  • Redeem a prepaid code
  • Payment is instant and you will receive a tax invoice straight away.
  • Your satisfaction is paramount: we offer a no-quibble refund guarantee.

* We guarantee each course to be available for at least 2 years from today, unless marked above with a date as a Course Retiring.

Online Courses