Power BI technique · built-in features only

How to build time intelligence once with calculation groups

By · Founder & Reliability Engineer · Updated

A calculation group applies the same logic to any measure. In Power BI Desktop's Model view, select Calculation group on the ribbon, add calculation items such as PY and YTD written with SELECTEDMEASURE(), then put the group's column on a matrix's columns or a slicer. Every measure in the visual gets the item's logic.

When to use it. Use calculation groups when you'd otherwise write Sales PY, Margin PY, Units PY and so on. Keep plain measures when there are only one or two.

Worked example

Worked example · Marrow & Pine · three measures × one Time Calc group (October 2026)

MeasureCurrentPYYoY %YTD
Revenue$412,000$368,000+12.0%$3,680,000
Orders5,1504,890+5.3%47,200
Avg Order Value80.0075.26+6.3%77.97
Measures on rows, the calculation group column on columns: four items, written once, applied to every measure.

Marrow & Pine is a fictional online homeware shop. The matrix shows three measures (Revenue, Orders and Avg Order Value) for October 2026 under four calculation items. Revenue is up +12.0% on October last year, orders are up +5.3%, and average order value is up +6.3%: customers are buying more often and spending a little more each time.

Year-to-date revenue is $3,680,000. The YTD column for Avg Order Value is 77.97, which is year-to-date revenue divided by year-to-date orders. It is not the average of the ten monthly order values (77.93). The gap is small here, but it shows how SELECTEDMEASURE works: the item re-evaluates the measure's own formula over the year-to-date dates, so a ratio stays a ratio instead of being summed or averaged.

No measure called Revenue PY, Orders YTD or AOV YoY exists in this model. Twelve cells come from three measures and four items.

Steps

  1. 1

    Mark a date table

    Time intelligence needs a contiguous Date table marked as a date table (Table tools → Mark as date table) and related to your facts.

  2. 2

    Create the group

    In Model view, select Calculation group on the Home ribbon. Power BI turns on the model setting that discourages implicit measures, so build visuals with explicit measures.

  3. 3

    Add calculation items

    Rename the group (Time Calc) and its column, then add items. Each uses SELECTEDMEASURE() in place of a specific measure.

    Current = SELECTEDMEASURE ()

PY = CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

YoY % =
VAR cur = SELECTEDMEASURE ()
VAR py = CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
RETURN DIVIDE ( cur - py, py )

YTD = CALCULATE ( SELECTEDMEASURE (), DATESYTD ( 'Date'[Date] ) )
  4. 4

    Set format strings per item

    YoY % should show as a percentage whatever the measure's format. Turn on the item's dynamic format string and return "0.0%"; other items keep SELECTEDMEASUREFORMATSTRING().

  5. 5

    Use it in a matrix

    Put measures on Values and the calculation group column on Columns. Each measure appears as Current, PY, YoY % and YTD.

How it works

A calculation item is a DAX expression that replaces the measure in the cell. SELECTEDMEASURE() stands for whichever measure the visual is evaluating, so CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( 'Date'[Date] ) ) means "this measure, one year earlier" for every measure on the visual. The calculation group itself is a table with one column; putting that column on a matrix or a slicer is what selects the items.

Format strings are part of the item. A dynamic format string lets YoY % always show as a percentage while Current, PY and YTD keep the measure's own format through SELECTEDMEASUREFORMATSTRING(). Without it, the YoY of a currency measure would show as a currency amount.

Calculation groups only apply to explicit measures. Dragging a numeric column into a visual creates an implicit measure that calculation items ignore, which is why Desktop discourages implicit measures once a group exists.

What Microsoft documents

Limits and behaviour from Microsoft Learn that change how you build this.

  • In Desktop, calculation groups are created in Model view with the Calculation group button on the ribbon, or written as TMDL in TMDL view. If Discourage implicit measures is off, Desktop asks to turn it on, because calculation items only apply to explicit measures. Existing implicit measures in visuals keep working, but you can no longer drag a numeric column into a visual's values.
  • Once a model has a calculation group, reports treat every measure as the variant data type. A measure that is reused as a dynamic format string can then show an error; Microsoft's fix is to wrap it in FORMAT ( [measure], "" ).
  • Text measures are a trap: a calculation item that multiplies or shifts SELECTEDMEASURE() breaks a dynamic title measure with "Cannot convert value … of type Text to type Numeric". Guard the item with ISNUMERIC ( SELECTEDMEASURE () ) and return the measure unchanged when it is text.
  • Sparklines in a table have a setting for calculation groups: Individual values (the default) or Entire sparkline. Arithmetic items are not supported with Entire sparkline.

Variations

  • Add a second group for currency conversion or scenario (Actual, Budget, Forecast) and set precedence so conversion happens before the time shift.
  • Use ISSELECTEDMEASURE inside an item to skip measures where it makes no sense, returning SELECTEDMEASURE() unchanged for them.

Check that it works

Put the calculation group column on a matrix's columns next to a measure you can verify by hand, such as one month's revenue. Check the PY cell against last year's number for the same month, and the YTD cell against a sum you calculate yourself. Then add a ratio measure and confirm the YTD ratio is recomputed, not summed.

Common mistakes

  • Calculation items apply to every measure in the visual, including ones where they make no sense (a YTD of a percentage).
  • Precedence matters when two calculation groups are used together; set the group's precedence.
  • Sort items with the ordinal property, or they appear alphabetically.
  • A measure that already shifts time (a hand-written Sales PY) gets shifted again by the PY item; keep base measures time-neutral.

FAQ

Do I need Tabular Editor?

No. Power BI Desktop's Model view can create and edit calculation groups. External tools remain useful for bulk edits.

Can a slicer pick the calculation item?

Yes. Put the group's column in a single-select slicer and every measure on the page follows it.

Sources

Menu names follow Power BI Desktop as documented on Microsoft Learn; they can move between releases. The companies and figures in the worked example are invented.

Related

Assets and guides that go with this technique.

More Power BI techniques

Make the report.
Then make it yours.

The Design Studio, templates and tools are free with no account (exports carry a small VizForge mark until you sign in). Sign up when you want AI: 5 credits on us, no card.

Generate a custom visualDesign a page free

Generated visuals are type-checked and built before you download them; one that fails the quality SLA refunds its credits. See all features or pricing.