Calculation Groups

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: 

	calculationItem 'Current' =
		SELECTEDMEASURE()
		ordinal: 0
		lineageTag: 

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

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: 

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

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

	calculationGroup 'Time Intelligence'
		precedence: 100
		lineageTag: 

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

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

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:

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

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: kpbray
• Source: kpbray/power-bi-agent-skills
• License: MIT

Install and usage instructions live in the source repository linked above.