kpbray 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-visualsskill for slicers/visuals - Test thoroughly - Verify with different base measures
- Document - Explain the calculation group purpose
- Validate - Use the
best-practicesskill to check quality