Menü
Creates calculation groups for reusable DAX patterns like time intelligence and currency conversion. Use to replace repetitive measures with dynamic calculations.
Mit Codex oder Claude installieren Kopieren Sie diesen Prompt, fügen Sie ihn in Codex, Claude oder einen anderen Assistant ein und lassen Sie die Skill-Seite prüfen und installieren.
Basierend auf der SOC-Berufsklassifikation
| name | calculation-groups |
| description | Creates calculation groups for reusable DAX patterns like time intelligence and currency conversion. Use to replace repetitive measures with dynamic calculations. |
This skill helps create calculation groups for reusable DAX patterns that can be applied dynamically to any measure.
| 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 |
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>
| 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 |
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>
When multiple calculation groups apply to the same measure:
| Precedence | Effect |
|---|---|
| Higher number | Applied later (outer calculation) |
| Lower number | Applied first (inner calculation) |
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
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
calculationItem 'Current' =
SELECTEDMEASURE()
formatStringDefinition: SELECTEDMEASUREFORMATSTRING()
calculationItem 'YoY %' =
DIVIDE(CurrentValue - PriorValue, PriorValue)
formatStringDefinition: "0.0%;-0.0%;0.0%"
calculationItem 'Variance' =
[Current] - [Budget]
formatStringDefinition:
VAR Value = SELECTEDMEASURE() - [Budget]
RETURN
IF(Value >= 0, "#,##0;(#,##0)", "#,##0;(#,##0)")
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"
}
}
}]
}
}
}
}
}
Put calculation items on rows/columns of a matrix:
{
"queryState": {
"Columns": {
"projections": [{
"field": {
"Column": {
"Expression": { "SourceRef": { "Entity": "Time Calculation" } },
"Property": "Time Calculation"
}
}
}]
}
}
}
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.
calculationItem 'vs Prior Period' =
VAR CurrentPeriodValue = SELECTEDMEASURE()
VAR PriorPeriodValue =
CALCULATE(
SELECTEDMEASURE(),
DATEADD(Date[Date], -1, MONTH)
)
RETURN
CurrentPeriodValue - PriorPeriodValue
formatStringDefinition: SELECTEDMEASUREFORMATSTRING()
calculationItem 'Rolling 3M Avg' =
AVERAGEX(
DATESINPERIOD(Date[Date], MAX(Date[Date]), -3, MONTH),
CALCULATE(SELECTEDMEASURE())
)
formatStringDefinition: SELECTEDMEASUREFORMATSTRING()
calculationItem 'Cumulative' =
CALCULATE(
SELECTEDMEASURE(),
FILTER(
ALL(Date),
Date[Date] <= MAX(Date[Date])
)
)
formatStringDefinition: SELECTEDMEASUREFORMATSTRING()
calculationItem '% of Total' =
DIVIDE(
SELECTEDMEASURE(),
CALCULATE(SELECTEDMEASURE(), REMOVEFILTERS())
)
formatStringDefinition: "0.0%"
calculationItem 'MAT' =
CALCULATE(
SELECTEDMEASURE(),
DATESINPERIOD(Date[Date], MAX(Date[Date]), -12, MONTH)
)
formatStringDefinition: SELECTEDMEASUREFORMATSTRING()
/// 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.
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
SELECTEDMEASUREFORMATSTRING() to preserve formatting/// commentsSome 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 |
After creating calculation groups:
report-visuals skill for slicers/visualsbest-practices skill to check qualityApplies Power BI best practices, BPA rules, and enterprise standards. Use for quality validation, naming conventions, and performance optimization.
Writes DAX measures, calculated columns, and calculations for Power BI. Use for business logic, time intelligence, and analytical calculations.
Implements CI/CD pipelines and DevOps practices for Power BI. Use for automated validation, testing, and deployment of PBIP projects.
Creates and manages Power BI Desktop Project (PBIP) structure. Use when starting new Power BI projects, setting up folder structure, or configuring project files.
Writes Power Query (M language) for data transformation, connections, and ETL. Use for data sources, transformations, parameters, and query optimization.
Creates Power BI reports and visuals in PBIR format. Use for pages, charts, tables, slicers, and visual configuration.