Skip to main content
Version: I2024.2.x

Referencing Calculation Groups in Calculated Measures

Measures within calculated measures can be scoped by the member range defined in a calculation group, typically a quantity of time like Month-to-Date or Equivalent Period in the Last Year.

To do this, you include references to calculation groups in your calculated measures. This enables the calculated measures to independently evaluate an expression's individual measure terms for each set defined in the calculation group, such as Distinct User Count, Month-To-Date, or Same Period Last Year.

To make this functionality as reusable as possible, you are not required to reference a specific hierarchy in your calculation group code. Additionally, calculation group expressions that reference specific levels (i.e ParallelPeriod or PeriodsToDate expressions) can be made generic by replacing specific level name references with generic level types. This makes your calculation groups more flexible, eliminating the need to create and maintain special-case calculations for each hierarchy.

Customizing calculation groups​

To use this functionality, you need to edit the boilerplate calculation selected for a calculation group to a) delete its hierarchy references, and b) replace level references with any of the following generic level types:

  • Time
  • TimeDays
  • TimeHalfYears
  • TimeQuarters
  • TimeSeconds
  • TimeWeeks
  • TimeYears
  • TimeTrimesters
  • TimeMonths
  • TimeHours
  • TimeMinutes

For queries that don’t include a qualified hierarchy, the system uses the dimension’s default hierarchy (the first hierarchy in the dimension). If you want to use a different hierarchy without changing your dimension, you can override the default using the query.planning.defaultHierarchyOverride cube-scoped setting. This setting defines a specific hierarchy to use in situations that require a qualified hierarchy, but none is present. Be aware that this only applies to the dimension that contains the specified hierarchy. Example value: [DateCustom].[StandardMonth].

note

It is not recommended to select multiple hierarchies from the same dimension as the calculation group modified to work with any hierarchy in the dimension. This is because the calculation group's hierarchy wild-card search resolves to the first hierarchy specified in the query. This limitation can be avoided by using unique queryIDs scoped by dimension for each hierarchy level and hierarchy in the dimension.

Examples​

The following examples illustrate how to edit existing calculation group calculations to make them work with any hierarchy in your time dimension.

Prior period

Before:

([DateCustom].[StandardMonth].CurrentMember.prevMember, [Measures].currentMember)

After:

([DateCustom].CurrentMember.prevMember, [Measures].currentMember)

Year to date

Before:

Aggregate(PeriodsToDate([DateCustom].[StandardMonth].[Year], [DateCustom].[StandardMonth].CurrentMember),[Measures].currentMember)

After:

Aggregate(PeriodsToDate([DateCustom].TimeYears, [DateCustom].CurrentMember),[Measures].currentMember)

Last four periods LY

Before:

Aggregate({ParallelPeriod([DateCustom].[StandardMonth].[Year], 1, [DateCustom].[StandardMonth].CurrentMember).Lag(3):ParallelPeriod([DateCustom].[StandardMonth].[Year], 1, [DateCustom].[StandardMonth].CurrentMember)}, [Measures].currentMember)

After:

Aggregate({ParallelPeriod([DateCustom].TimeYears, 1, [DateCustom].CurrentMember).Lag(3):ParallelPeriod([DateCustom].TimeYears, 1, [DateCustom].CurrentMember)}, [Measures].currentMember)
note

This calculation group generic syntax only works for references to "CurrentMember". References to the ALL member used in the Percent of Total, or Parent used in the Percent of Parent template calculations are not supported.

Calculated measure syntax used to reference calculation groups​

The following examples illustrate the tuple syntax in calculated measures needed to use this functionality:

Month to Date

Before:

AGGREGATE(MTD([DateCustom].[StandardMonth].CurrentMember),[Measures].[Internet Sales Amount Local]) / AGGREGATE(MTD([DateCustom].[StandardMonth].CurrentMember), [Measures].[Internet Order Count])

After:

([Measures].[ISA], [TimeDim].[TimeCalcs].CurrentMember) / ([Measures].[IOC], [TimeDim].[TimeCalcs].CurrentMember)