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

24 February 2014 · · 3093 views

Relative Tuple & Set Patterns

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 to play the video.

  • MDX Basic Concepts 29-min Watch with Free Subscription

  • MDX Queries 24-min Watch with Free Subscription

  • MDX Calculated Members 52-min

  • MDX: Member and Set Functions 38-min

  • What is the MDX Current Member Function? 5-min Free—Watch Now

  • MDX Numeric Functions and CURRENTMEMBER 33-min

  • MDX: Previous Period Growths, Shares & Moving Averages 38-min

  • More MDX Member & Set Functions 1-hour 37-min

  • What is a Subselect in MDX? 3-min Watch with Free Subscription

  • Exists, MDX Solve Order, and Subselects 49-min

  • MDX Performance Diagnosis with Profiler 29-min

Purchase a Full Access Subscription

Individual Subscription


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

from $480/year

For small business & enterprise.
Group Purchase
  • You can also redeem a prepaid code.
  • Payments are instant and you will receive a tax invoice straight away.
  • We offer sales quotes/pro-forma invoices, and we accept purchase orders and bank transfers.
  • Your satisfaction is paramount: we offer a no-quibble refund guarantee.
  • See pricing FAQ for more detail.
In collaboration with
Project Botticelli logo Oxford Computer Training logo SQLBI logo Prodata logo