| name | calc-groups |
| description | Reference for Power BI Calculation Groups in TMDL. Use this whenever a user asks for time intelligence patterns (YTD, QTD, rolling periods, prior year), scenario switching (Actual/Budget/Forecast), currency conversion, or any pattern where the same transformation needs to apply dynamically across many measures. Always consult this before writing individual YTD/PY/Rolling measures from scratch — a calc group is the correct architecture and eliminates boilerplate at scale.
|
| version | 1.5 |
Calculation Groups — Power BI / TMDL Reference
Calculation groups intercept SELECTEDMEASURE() and transform it, applying the
same logic across every measure in the model without duplicating DAX.
TMDL Structure
calculationItem entries are nested inside calculationGroup at 3 TABs.
column entries are siblings of calculationGroup at 2 TABs.
createOrReplace ← root
table 'Name' ← 1 TAB
calculationGroup ← 2 TABs
precedence: … ← 3 TABs
calculationItem 'Name' = DAX_inline ← 3 TABs (single-line)
calculationItem 'Name' = ← 3 TABs (multi-line)
DAX body ← 5 TABs (jumps TWO levels)
ordinal: … ← 4 TABs
column Period ← 2 TABs (sibling of calculationGroup)
dataType: … ← 3 TABs
column Ordinal ← 2 TABs
dataType: … ← 3 TABs
Key rules:
- Multi-line
calculationItem uses = on the declaration line — no expression: property
- DAX body jumps TWO levels deeper (3 TABs → 5 TABs), same as measures
- Properties (
ordinal:) sit at 4 TABs — one level below the declaration
sourceColumn: Name — no brackets- Always include a separate
Ordinal column with sortByColumn: Ordinal on the Name column
Time Intelligence Calculation Group
createOrReplace
table 'Time Intelligence'
calculationGroup
precedence: 10
calculationItem 'None' = SELECTEDMEASURE()
calculationItem 'Current' = SELECTEDMEASURE()
calculationItem 'YTD' =
CALCULATE(
SELECTEDMEASURE(),
DATESYTD('Date'[Date])
)
ordinal: 2
calculationItem 'QTD' =
CALCULATE(
SELECTEDMEASURE(),
DATESQTD('Date'[Date])
)
ordinal: 3
calculationItem 'MTD' =
CALCULATE(
SELECTEDMEASURE(),
DATESMTD('Date'[Date])
)
ordinal: 4
calculationItem 'PY' =
CALCULATE(
SELECTEDMEASURE(),
SAMEPERIODLASTYEAR('Date'[Date])
)
ordinal: 5
calculationItem 'PY YTD' =
CALCULATE(
SELECTEDMEASURE(),
SAMEPERIODLASTYEAR(DATESYTD('Date'[Date]))
)
ordinal: 6
calculationItem 'vs PY' =
SELECTEDMEASURE()
- CALCULATE(
SELECTEDMEASURE(),
SAMEPERIODLASTYEAR('Date'[Date])
)
ordinal: 7
calculationItem 'vs PY %' =
VAR CurrentValue = SELECTEDMEASURE()
VAR PreviousYearValue =
CALCULATE(
SELECTEDMEASURE(),
SAMEPERIODLASTYEAR('Date'[Date])
)
VAR DiffValue = CurrentValue - PreviousYearValue
RETURN DIVIDE(DiffValue, PreviousYearValue)
ordinal: 8
calculationItem 'Rolling 3M' =
CALCULATE(
SELECTEDMEASURE(),
DATESINPERIOD('Date'[Date], LASTDATE('Date'[Date]), -3, MONTH)
)
ordinal: 9
calculationItem 'Rolling 12M' =
CALCULATE(
SELECTEDMEASURE(),
DATESINPERIOD('Date'[Date], LASTDATE('Date'[Date]), -12, MONTH)
)
ordinal: 10
column Period
dataType: string
isDefaultLabel
sourceColumn: Name
sortByColumn: Ordinal
summarizeBy: none
column Ordinal
dataType: int64
formatString: 0
sourceColumn: Ordinal
summarizeBy: sum
LASTDATE vs TODAY(): Always anchor rolling windows to LASTDATE('Date'[Date]),
not TODAY(). LASTDATE respects the visual's date filter; TODAY() ignores it.
Scenario / Budget Calculation Group
createOrReplace
table Scenario
calculationGroup
precedence: 20
calculationItem 'None' = SELECTEDMEASURE()
calculationItem 'Actual' =
CALCULATE(
SELECTEDMEASURE(),
FactTable[Scenario] = "Actual"
)
ordinal: 1
calculationItem 'Budget' =
CALCULATE(
SELECTEDMEASURE(),
FactTable[Scenario] = "Budget"
)
ordinal: 2
calculationItem 'Forecast' =
CALCULATE(
SELECTEDMEASURE(),
FactTable[Scenario] = "Forecast"
)
ordinal: 3
calculationItem 'vs Budget' =
VAR _actual =
CALCULATE(SELECTEDMEASURE(), FactTable[Scenario] = "Actual")
VAR _budget =
CALCULATE(SELECTEDMEASURE(), FactTable[Scenario] = "Budget")
RETURN _actual - _budget
ordinal: 4
calculationItem 'vs Budget %' =
VAR _actual =
CALCULATE(SELECTEDMEASURE(), FactTable[Scenario] = "Actual")
VAR _budget =
CALCULATE(SELECTEDMEASURE(), FactTable[Scenario] = "Budget")
RETURN DIVIDE(_actual - _budget, _budget)
ordinal: 5
column Scenario
dataType: string
isDefaultLabel
sourceColumn: Name
sortByColumn: Ordinal
summarizeBy: none
column Ordinal
dataType: int64
formatString: 0
sourceColumn: Ordinal
summarizeBy: sum
Currency Conversion Calculation Group
createOrReplace
table Currency
calculationGroup
precedence: 5
calculationItem 'USD' = SELECTEDMEASURE()
calculationItem 'EUR' =
VAR _rate =
CALCULATE(
AVERAGE(ExchangeRates[Rate]),
ExchangeRates[CurrencyCode] = "EUR"
)
RETURN SELECTEDMEASURE() * _rate
ordinal: 2
calculationItem 'GBP' =
VAR _rate =
CALCULATE(
AVERAGE(ExchangeRates[Rate]),
ExchangeRates[CurrencyCode] = "GBP"
)
RETURN SELECTEDMEASURE() * _rate
ordinal: 3
calculationItem 'JPY' =
VAR _rate =
CALCULATE(
AVERAGE(ExchangeRates[Rate]),
ExchangeRates[CurrencyCode] = "JPY"
)
RETURN SELECTEDMEASURE() * _rate
ordinal: 4
column Currency
dataType: string
isDefaultLabel
sourceColumn: Name
sortByColumn: Ordinal
summarizeBy: none
column Ordinal
dataType: int64
formatString: 0
sourceColumn: Ordinal
summarizeBy: sum
Precedence
Higher number = applied first (outermost). Lower number wraps last.
Precedence 20 → Scenario (outermost)
Precedence 10 → Time (middle)
Precedence 5 → Currency (innermost)
Excluding Specific Measures
calculationItem 'YTD' =
IF(
ISSELECTEDMEASURE([# Headcount], [Balance]),
SELECTEDMEASURE(),
CALCULATE(SELECTEDMEASURE(), DATESYTD('Date'[Date]))
)
ordinal: 2
Checklist Before Publishing
Related References
- tmdl-standards/SKILL.md — TMDL syntax ground rules: indentation, createOrReplace wrapper, property order
- measures/SKILL.md — Base measures that calc group items operate on; naming and folder conventions
- dax-udf/SKILL.md — UDFs that can be called from within calculationItem expressions
- svg-measures/SKILL.md — SVG icon badges reflecting the active calc group selection (e.g. period label)
Version: 1.5
Last Updated: 2026-05-02
