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

tmdl

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:

tmdl

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

tmdl

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

tmdl

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

tmdl

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

Custom Format

tmdl

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

Conditional Format

tmdl

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:

json

{
  "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:

json

{
  "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():

tmdl

calculationItem 'Current' =
	SELECTEDMEASURE()
	ordinal: 0

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

Advanced Patterns

Period Over Period Comparison

tmdl

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

Rolling Calculations

tmdl

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

Cumulative Total

tmdl

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

Percentage of Total

tmdl

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

Moving Annual Total

tmdl

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

Precedence Examples

Time + Currency

tmdl

/// 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":

tmdl

/// 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