Calculation Groups Skill

This skill helps create calculation groups for reusable DAX patterns that can be applied dynamically to any measure.

When to Use This Skill

Replacing repetitive time intelligence measures (YTD, PY, YoY for every base measure)
Creating currency conversion logic
Building dynamic period comparisons
Implementing conditional formatting patterns
Creating calculation templates for multiple measures

Benefits of Calculation Groups

Without Calculation Groups	With Calculation Groups
Sales YTD, Cost YTD, Profit YTD, Revenue YTD...	1 base measure + Time Intelligence group
4 base × 5 time calcs = 20 measures	4 base + 5 calc items = 9 objects
Hard to maintain	Single point of change

Calculation Group Structure

TMDL Syntax

calculationGroup 'Time Intelligence'
	precedence: 100
	lineageTag: <guid>

	calculationItem 'Current' =
		SELECTEDMEASURE()
		ordinal: 0
		lineageTag: <guid>

	calculationItem 'YTD' =
		CALCULATE(
		    SELECTEDMEASURE(),
		    DATESYTD(Date[Date])
		)
		ordinal: 1
		lineageTag: <guid>

Key Concepts

Concept	Description
`calculationGroup`	Container for calculation items
`calculationItem`	Individual calculation pattern
`SELECTEDMEASURE()`	Reference to whatever measure is being modified
`precedence`	Order when multiple groups applied
`ordinal`	Order of items in slicer/dropdown

Creating a Calculation Group

Step 1: Create the Table

Calculation groups require a table with a single column:

table 'Time Calculation'
	lineageTag: <guid>

	column 'Time Calculation'
		dataType: string
		isHidden: false
		summarizeBy: none
		sortByColumn: 'Time Calculation Order'
		lineageTag: <guid>

	column 'Time Calculation Order'
		dataType: int64
		isHidden: true
		summarizeBy: none
		lineageTag: <guid>

	calculationGroup 'Time Intelligence'
		precedence: 100
		lineageTag: <guid>

		calculationItem 'Current' =
			SELECTEDMEASURE()
			ordinal: 0
			formatStringDefinition: SELECTEDMEASUREFORMATSTRING()
			lineageTag: <guid>

		calculationItem 'YTD' =
			CALCULATE(
			    SELECTEDMEASURE(),
			    DATESYTD(Date[Date])
			)
			ordinal: 1
			formatStringDefinition: SELECTEDMEASUREFORMATSTRING()
			lineageTag: <guid>

Step 2: Configure Precedence

When multiple calculation groups apply to the same measure:

Precedence	Effect
Higher number	Applied later (outer calculation)
Lower number	Applied first (inner calculation)

Example:

Currency (precedence: 50) - converts currency first
Time Intelligence (precedence: 100) - then applies time calculation

Time Intelligence Calculation Group

Complete Example

table 'Time Calculation'
	lineageTag: a1b2c3d4-e5f6-7890-1111-000000000001

	column 'Time Calculation'
		dataType: string
		summarizeBy: none
		sortByColumn: 'Time Calculation Order'
		lineageTag: a1b2c3d4-e5f6-7890-1111-000000000002

	column 'Time Calculation Order'
		dataType: int64
		isHidden
		summarizeBy: none
		lineageTag: a1b2c3d4-e5f6-7890-1111-000000000003

	calculationGroup 'Time Intelligence'
		precedence: 100
		lineageTag: a1b2c3d4-e5f6-7890-1111-000000000004

		/// Current period value (no modification)
		calculationItem 'Current' =
			SELECTEDMEASURE()
			ordinal: 0
			formatStringDefinition: SELECTEDMEASUREFORMATSTRING()
			lineageTag: a1b2c3d4-e5f6-7890-1111-000000000010

		/// Year-to-date calculation
		calculationItem 'YTD' =
			CALCULATE(
			    SELECTEDMEASURE(),
			    DATESYTD(Date[Date])
			)
			ordinal: 1
			formatStringDefinition: SELECTEDMEASUREFORMATSTRING()
			lineageTag: a1b2c3d4-e5f6-7890-1111-000000000011

		/// Month-to-date calculation
		calculationItem 'MTD' =
			CALCULATE(
			    SELECTEDMEASURE(),
			    DATESMTD(Date[Date])
			)
			ordinal: 2
			formatStringDefinition: SELECTEDMEASUREFORMATSTRING()
			lineageTag: a1b2c3d4-e5f6-7890-1111-000000000012

		/// Quarter-to-date calculation
		calculationItem 'QTD' =
			CALCULATE(
			    SELECTEDMEASURE(),
			    DATESQTD(Date[Date])
			)
			ordinal: 3
			formatStringDefinition: SELECTEDMEASUREFORMATSTRING()
			lineageTag: a1b2c3d4-e5f6-7890-1111-000000000013

		/// Prior year value
		calculationItem 'PY' =
			CALCULATE(
			    SELECTEDMEASURE(),
			    SAMEPERIODLASTYEAR(Date[Date])
			)
			ordinal: 4
			formatStringDefinition: SELECTEDMEASUREFORMATSTRING()
			lineageTag: a1b2c3d4-e5f6-7890-1111-000000000014

		/// Prior year YTD
		calculationItem 'PY YTD' =
			CALCULATE(
			    SELECTEDMEASURE(),
			    SAMEPERIODLASTYEAR(Date[Date]),
			    DATESYTD(Date[Date])
			)
			ordinal: 5
			formatStringDefinition: SELECTEDMEASUREFORMATSTRING()
			lineageTag: a1b2c3d4-e5f6-7890-1111-000000000015

		/// Year-over-year change
		calculationItem 'YoY' =
			VAR CurrentValue = SELECTEDMEASURE()
			VAR PriorValue = CALCULATE(
			    SELECTEDMEASURE(),
			    SAMEPERIODLASTYEAR(Date[Date])
			)
			RETURN
			    CurrentValue - PriorValue
			ordinal: 6
			formatStringDefinition: SELECTEDMEASUREFORMATSTRING()
			lineageTag: a1b2c3d4-e5f6-7890-1111-000000000016

		/// Year-over-year percentage change
		calculationItem 'YoY %' =
			VAR CurrentValue = SELECTEDMEASURE()
			VAR PriorValue = CALCULATE(
			    SELECTEDMEASURE(),
			    SAMEPERIODLASTYEAR(Date[Date])
			)
			RETURN
			    DIVIDE(CurrentValue - PriorValue, PriorValue)
			ordinal: 7
			formatStringDefinition: "0.0%;-0.0%;0.0%"
			lineageTag: a1b2c3d4-e5f6-7890-1111-000000000017

Currency Conversion Group

table 'Currency'
	lineageTag: b2c3d4e5-f6a7-8901-2222-000000000001

	column 'Currency'
		dataType: string
		summarizeBy: none
		lineageTag: b2c3d4e5-f6a7-8901-2222-000000000002

	calculationGroup 'Currency Conversion'
		precedence: 50
		lineageTag: b2c3d4e5-f6a7-8901-2222-000000000003

		/// No conversion (base currency)
		calculationItem 'USD' =
			SELECTEDMEASURE()
			ordinal: 0
			formatStringDefinition: "$#,##0.00"
			lineageTag: b2c3d4e5-f6a7-8901-2222-000000000010

		/// Convert to EUR
		calculationItem 'EUR' =
			SELECTEDMEASURE() * 0.92
			ordinal: 1
			formatStringDefinition: "€#,##0.00"
			lineageTag: b2c3d4e5-f6a7-8901-2222-000000000011

		/// Convert to GBP
		calculationItem 'GBP' =
			SELECTEDMEASURE() * 0.79
			ordinal: 2
			formatStringDefinition: "£#,##0.00"
			lineageTag: b2c3d4e5-f6a7-8901-2222-000000000012

		/// Convert using rate table
		calculationItem 'Dynamic' =
			VAR SelectedCurrency = SELECTEDVALUE(CurrencySelection[Currency], "USD")
			VAR Rate = LOOKUPVALUE(
			    ExchangeRates[Rate],
			    ExchangeRates[Currency], SelectedCurrency
			)
			RETURN
			    SELECTEDMEASURE() * Rate
			ordinal: 3
			lineageTag: b2c3d4e5-f6a7-8901-2222-000000000013

Format String Expressions

Preserving Original Format

calculationItem 'Current' =
	SELECTEDMEASURE()
	formatStringDefinition: SELECTEDMEASUREFORMATSTRING()

Custom Format

calculationItem 'YoY %' =
	DIVIDE(CurrentValue - PriorValue, PriorValue)
	formatStringDefinition: "0.0%;-0.0%;0.0%"

Conditional Format

calculationItem 'Variance' =
	[Current] - [Budget]
	formatStringDefinition:
		VAR Value = SELECTEDMEASURE() - [Budget]
		RETURN
		    IF(Value >= 0, "#,##0;(#,##0)", "#,##0;(#,##0)")

Using Calculation Groups in Reports

As Slicer

Add the calculation group column to a slicer to let users choose the calculation:

{
  "visual": {
    "visualType": "slicer",
    "query": {
      "queryState": {
        "Values": {
          "projections": [{
            "field": {
              "Column": {
                "Expression": { "SourceRef": { "Entity": "Time Calculation" } },
                "Property": "Time Calculation"
              }
            }
          }]
        }
      }
    }
  }
}

On Axis

Put calculation items on rows/columns of a matrix:

{
  "queryState": {
    "Columns": {
      "projections": [{
        "field": {
          "Column": {
            "Expression": { "SourceRef": { "Entity": "Time Calculation" } },
            "Property": "Time Calculation"
          }
        }
      }]
    }
  }
}

Default Selection

To default to "Current" when no item selected, use ISSELECTEDMEASURE():

calculationItem 'Current' =
	SELECTEDMEASURE()
	ordinal: 0

With ordinal 0, "Current" appears first and is often the default.

Advanced Patterns

Period Over Period Comparison

calculationItem 'vs Prior Period' =
	VAR CurrentPeriodValue = SELECTEDMEASURE()
	VAR PriorPeriodValue =
	    CALCULATE(
	        SELECTEDMEASURE(),
	        DATEADD(Date[Date], -1, MONTH)
	    )
	RETURN
	    CurrentPeriodValue - PriorPeriodValue
	formatStringDefinition: SELECTEDMEASUREFORMATSTRING()

Rolling Calculations

calculationItem 'Rolling 3M Avg' =
	AVERAGEX(
	    DATESINPERIOD(Date[Date], MAX(Date[Date]), -3, MONTH),
	    CALCULATE(SELECTEDMEASURE())
	)
	formatStringDefinition: SELECTEDMEASUREFORMATSTRING()

Cumulative Total

calculationItem 'Cumulative' =
	CALCULATE(
	    SELECTEDMEASURE(),
	    FILTER(
	        ALL(Date),
	        Date[Date] <= MAX(Date[Date])
	    )
	)
	formatStringDefinition: SELECTEDMEASUREFORMATSTRING()

Percentage of Total

calculationItem '% of Total' =
	DIVIDE(
	    SELECTEDMEASURE(),
	    CALCULATE(SELECTEDMEASURE(), REMOVEFILTERS())
	)
	formatStringDefinition: "0.0%"

Moving Annual Total

calculationItem 'MAT' =
	CALCULATE(
	    SELECTEDMEASURE(),
	    DATESINPERIOD(Date[Date], MAX(Date[Date]), -12, MONTH)
	)
	formatStringDefinition: SELECTEDMEASUREFORMATSTRING()

Precedence Examples

Time + Currency

/// Apply currency first (lower precedence)
calculationGroup 'Currency'
	precedence: 50

/// Then apply time intelligence (higher precedence)
calculationGroup 'Time Intelligence'
	precedence: 100

Result: Currency conversion happens first, then time calculation applies.

Multiple Time Comparisons

If you have both "Time Period" and "Time Comparison":

/// Base time period selection
calculationGroup 'Time Period'
	precedence: 100

/// Comparison calculations
calculationGroup 'Time Comparison'
	precedence: 200

Boundaries and Constraints

DO

Always include a "Current" or "Actual" item for base value
Use SELECTEDMEASUREFORMATSTRING() to preserve formatting
Set appropriate ordinal for logical ordering
Use meaningful names for items
Add descriptions with /// comments
Test with multiple base measures

DO NOT

Don't create too many calculation items (max 10-15)
Don't apply to non-additive measures without testing
Avoid complex logic that varies by measure type
Don't forget format string expressions
Never use calculation groups for one-off calculations

Measures That May Not Work

Some measures don't work well with calculation groups:

Measure Type	Issue
Distinct count	Time intelligence may not apply correctly
Average	YTD average behavior may be unexpected
Ratios	Results may be meaningless
Semi-additive	Special handling needed

Workflow Integration

After creating calculation groups:

Add to report - Use the report-visuals skill for slicers/visuals
Test thoroughly - Verify with different base measures
Document - Explain the calculation group purpose
Validate - Use the best-practices skill to check quality