MoM, MoM%, YoY, and YoY% DAX measure approaches

There are many ways to get month-over-month and year-over-year comparisons in Power BI, but when you need them for multiple measures it can get unwieldy fast. I'm going to walk through how I set up a calculation group to handle MoM, MoM %, YoY, and YoY % — and why it makes life so much easier when changes come along later.

The scenario

I have a model with a Financials fact table and a Date table. My data starts in September 2025 and runs through December 2026. I need time comparisons for six base measures: Units, Sales, COGS, Gross Sales, Discounts, and Profit.

That's 24 time intelligence measures. Let's not write the same logic 24 times.

Start with a proper Date table

Before any time intelligence works reliably, you need a dedicated Date table. Not the date column sitting in your fact table — a separate dimension table marked as a date table. This matters for a few reasons:

DAX time intelligence functions require it. Functions like SAMEPERIODLASTYEAR, DATEADD, and TOTALYTD need a contiguous column of dates in a table marked as a date table. They won't work on a date column in a fact table that has gaps.

It gives you clean columns to slice by. Year, Month Number, Month Name, Quarter — all calculated once in the Date table and available to any visual. No duplicating those columns across fact tables.

It replaces auto date/time. Power BI's auto date/time feature creates hidden date tables behind the scenes for every date column. That's extra memory, extra processing, and you can't control what's in them. With your own Date table, you turn that off and own the logic.

Make sure to add the relationship to your date column. Your fact table's date column relates to the Date table with a many-to-one relationship. Filter the Date table, and it flows through to the facts. This is how a month slicer filters Sales without needing date columns in the Financials table.

Here's the simple one I'm using for this example, as a calculated table:

Date =

VAR _MinDate = DATE(2025, 1, 1)

VAR _MaxDate = DATE(2026, 12, 31)

RETURN

ADDCOLUMNS(

CALENDAR(_MinDate, _MaxDate),

"Year", YEAR([Date]),

"Month Number", MONTH([Date]),

"Month Name", FORMAT([Date], "MMM"),

"Month Year", FORMAT([Date], "MMM YYYY"),

"Quarter", "Q" & FORMAT([Date], "Q"),

"Year Month", YEAR([Date]) * 100 + MONTH([Date])

)

Mark it as a date table in the model ('Date'[Date] as the date column), create a relationship from Financials[Date] to Date[Date], and you're set.

What's a calculation group?

If you haven't used calculation groups before, think of them as reusable measure modifiers. You define a piece of logic once using SELECTEDMEASURE() — a placeholder that becomes whatever measure is being evaluated at runtime. When you apply the calculation group item to a measure, the logic wraps around it.

I created a calculation group called "Time Calculation" with five items: Actual, MoM, MoM %, YoY, and YoY %.

How individual measures use the calculation group

This is the fun part. Each time intelligence measure becomes a one-liner. Here's Units YoY:

// Apply YoY calculation group item to Units

// Compares to same period last year; at totals only includes overlapping months

Units YoY = CALCULATE([Units], 'Time Calculation'[Time Calculation] = "YoY")

That's it! The CALCULATE tells Power BI to apply the "YoY" calculation item to [Units]. Inside the calculation group, SELECTEDMEASURE() becomes [Units] and the YoY logic runs.

Every time intelligence measure follows the same pattern:

// Apply MoM calculation group item to Sales

Sales MoM = CALCULATE([Sales], 'Time Calculation'[Time Calculation] = "MoM")

// Apply MoM % calculation group item to Sales

Sales MoM % = CALCULATE([Sales], 'Time Calculation'[Time Calculation] = "MoM %")

// Apply YoY calculation group item to COGS

COGS YoY = CALCULATE([COGS], 'Time Calculation'[Time Calculation] = "YoY")

// Apply YoY % calculation group item to Profit

Profit YoY % = CALCULATE([Profit], 'Time Calculation'[Time Calculation] = "YoY %")

The base measure changes. The calculation group item changes. The logic stays in one place.

Let's look at what that logic actually does.

How MoM works

MoM answers a simple question: how does the most recent month compare to the month before it?

Here's the approach:

1. Find the latest month in context. If your visual is showing Oct 2026, the latest month is October. If it's showing all of 2026, the latest month is December.

2. Figure out the prior month. Subtract one. If we're in January, wrap back to December of the year before.

3. Check if the prior month exists. My data starts in September 2025, so there's no August 2025 to compare to. I use REMOVEFILTERS() — removing all filters including Segment, Product, everything — to check if the prior month has any data at all. If not, return BLANK.

4. Subtract. Current minus prior.

There's one subtlety worth calling out. When getting the current and prior values I add + 0 to each. Why? Say a specific product didn't sell in November but did sell in December. Without + 0, that product's November value would be BLANK, and BLANK minus something is BLANK, not a number. So that product's MoM would show BLANK and wouldn't contribute to the subtotal. Adding zero converts BLANK to 0, so every product participates and the detail rows add up to the total. A small thing, but it makes a big difference when you're looking at a table with product breakdowns.

Let's look at an example. Here's Government segment by product:

Government	Units	Units MoM
Amarilla	68,045	-6,509
Carretera	54,912	12,014
Montana	64,657	0
Paseo	146,799	10,668
Velo	72,164	-5,410
VTT	64,099	12,977
Total	470,674	23,740

Montana shows 0 (not BLANK) because it sold units in December but didn't in November — the + 0 made that work. And -6,509 + 12,014 + 0 + 10,668 + (-5,410) + 12,977 = 23,740. The products add up to the total.

How MoM % works

MoM % answers: by what percentage did the latest month change from the prior month?

The logic is the same as MoM for finding the current and prior month values. The only difference is the last step. Instead of subtracting, it divides:

DIVIDE(Current - Prior, Prior)

So if Sales went from $100K in November to $120K in December, MoM returns $20K and MoM % returns 20%. If Sales dropped from $100K to $80K, MoM returns -$20K and MoM % returns -20%.

The same BLANK and + 0 rules apply here too. No prior month? BLANK. Product didn't sell last month? Treated as zero.

How YoY works

YoY answers: how does this period compare to the same period last year?

This one has a twist. My data starts in September 2025. If I compare all of 2026 (12 months of data) to 2025 (only 4 months of data), the comparison is meaningless. 2026 would look dramatically higher just because it has more months.

So instead of comparing year totals directly, I go month by month:

1. Get every Year + Month Number combination in the current filter context

2. For each one, look up the current year value and the prior year value

3. If both exist, include the difference. If either is missing, skip that month entirely

4. Sum up all the included differences

Let's walk through what that looks like for the 2026 year total:

Month	2026 Units	2025 Units	Both exist?	Difference
Jan	67,836	—	No	skipped
Feb	55,115	—	No	skipped
Mar	53,420	—	No	skipped
...	...	—	No	skipped
Sep	57,280	50,601	Yes	+6,679
Oct	105,482	95,622	Yes	+9,860
Nov	55,650	65,481	Yes	-9,831
Dec	102,336	52,970	Yes	+49,366
			YoY Total	56,074

Only September through December contribute — the months with data in both years. Fair, apples-to-apples.

At the individual month level this naturally works too — the iteration just has one row. Oct 2026 vs Oct 2025 = +9,860.

How YoY % works

YoY % answers: by what percentage did the current period change from the same period last year?

You might think you could just divide the YoY value by the prior year total. But which prior year total? The full 2025 total includes only Sep–Dec, while the full 2026 total includes Jan–Dec. If I divided the YoY difference by the complete 2025 total, I'd get the right answer — but only by coincidence, because 2025 happens to only have the overlapping months. In other datasets that won't be the case.

So YoY % builds two filtered totals using the same month-by-month iteration:

• Filtered current total: Sum up 2026 values, but only for months that also have 2025 data

• Filtered prior total: Sum up 2025 values, same months

Then divide:

DIVIDE(FilteredCurrent - FilteredPrior, FilteredPrior)

Using the same example: Sep–Dec 2026 totals 320,748 units. Sep–Dec 2025 totals 264,674 units. YoY % = (320,748 - 264,674) / 264,674 = 21%.

At the single-month level it's straightforward — Oct 2026 (105,482) vs Oct 2025 (95,622) = 10%. But at totals, those filtered sums are what keep the comparison honest.

Why keep it in a calculation group?

Let's say your business later decides: "We want YoY to handle partial months too — if today is the 15th, only compare the first 15 days of this month to the first 15 days of the same month last year."

Without a calculation group, you'd open up all 6 YoY measures and all 6 YoY % measures and make the same change 12 times. Hope you don't miss one.

With the calculation group, you update the YoY item and the YoY % item. Two edits. All 12 measures that reference them pick up the change instantly.

Approach	Measures to maintain	Places to update logic
Individual measures	24 complex formulas	24
Calculation group	24 one-liners + 5 formulas in the group	5

It's the same principle as defining measures in a model instead of using implicit measures — centralize the logic, reuse it everywhere. The calculation group is just doing that one level higher.

The TMDL

Here is the full calculation group in TMDL format. You can paste this directly into the TMDL view in Power BI Desktop. I've added inline comments throughout so anyone reading the model can understand what each step is doing and why.

ref table 'Time Calculation'

calculationGroup

precedence: 1

noSelectionExpression = SELECTEDMEASURE()

calculationItem Actual = SELECTEDMEASURE()

calculationItem MoM =

// MoM: Compare the latest month in context to the month before it.

// At any level (month row, year total, grand total), finds the most recent

// month and returns Current - Prior. Returns BLANK if no prior month exists.

// Step 1: Find the latest month in whatever date context we're in

VAR _LastYear = MAX('Date'[Year])

VAR _LastMonth = MAXX(FILTER('Date', 'Date'[Year] = _LastYear), 'Date'[Month Number])

// Step 2: Calculate the prior month (wrap Jan back to Dec of prior year)

VAR _PrevYear = IF(_LastMonth = 1, _LastYear - 1, _LastYear)

VAR _PrevMonth = IF(_LastMonth = 1, 12, _LastMonth - 1)

// Step 3: Check if the prior month has ANY data at all.

// REMOVEFILTERS() clears ALL filters (including Segment, Product, etc.)

// so we're checking globally, not just for this slice.

// Without this, Sep 2025 would compare to a nonexistent Aug 2025.

VAR _PriorMonthExists =

CALCULATE(

SELECTEDMEASURE(),

REMOVEFILTERS(),

'Date'[Year] = _PrevYear,

'Date'[Month Number] = _PrevMonth

)

// Step 4: Get current and prior values for this specific context.

// REMOVEFILTERS('Date') clears only date filters, preserving Segment/Product/etc.

// The + 0 converts BLANK to 0 so products that didn't sell last month

// still contribute to the subtotal (BLANK wouldn't participate in SUM).

VAR _CurrValue =

CALCULATE(

SELECTEDMEASURE(),

REMOVEFILTERS('Date'),

'Date'[Year] = _LastYear,

'Date'[Month Number] = _LastMonth

) + 0

VAR _PrevValue =

CALCULATE(

SELECTEDMEASURE(),

REMOVEFILTERS('Date'),

'Date'[Year] = _PrevYear,

'Date'[Month Number] = _PrevMonth

) + 0

// Step 5: Return difference, or BLANK if prior month doesn't exist

RETURN

IF(

ISBLANK(_PriorMonthExists),

BLANK(),

_CurrValue - _PrevValue

)

calculationItem 'MoM %' =

// MoM %: Same logic as MoM, but returns the percentage change.

// DIVIDE handles division by zero safely (returns BLANK).

VAR _LastYear = MAX('Date'[Year])

VAR _LastMonth = MAXX(FILTER('Date', 'Date'[Year] = _LastYear), 'Date'[Month Number])

VAR _PrevYear = IF(_LastMonth = 1, _LastYear - 1, _LastYear)

VAR _PrevMonth = IF(_LastMonth = 1, 12, _LastMonth - 1)

// Check globally if prior month has data (ignoring all dimension filters)

VAR _PriorMonthExists =

CALCULATE(

SELECTEDMEASURE(),

REMOVEFILTERS(),

'Date'[Year] = _PrevYear,

'Date'[Month Number] = _PrevMonth

)

// Get values with + 0 to convert BLANK to 0 for proper subtotal rollup

VAR _CurrValue =

CALCULATE(

SELECTEDMEASURE(),

REMOVEFILTERS('Date'),

'Date'[Year] = _LastYear,

'Date'[Month Number] = _LastMonth

) + 0

VAR _PrevValue =

CALCULATE(

SELECTEDMEASURE(),

REMOVEFILTERS('Date'),

'Date'[Year] = _PrevYear,

'Date'[Month Number] = _PrevMonth

) + 0

// Return percentage change instead of raw difference

RETURN

IF(

ISBLANK(_PriorMonthExists),

BLANK(),

DIVIDE(_CurrValue - _PrevValue, _PrevValue)

)

formatStringDefinition = "#,0%;-#,0%;#,0%"

calculationItem YoY =

// YoY: Compare current period to the same period last year.

// Handles partial years by only including months that have data in BOTH years.

// Works at any level: single month, year total, or grand total.

VAR _CurrentValue = SELECTEDMEASURE()

// Get every distinct Year + Month in the current filter context.

// At a month row this is one row; at a year total it's all months in that year.

VAR _MonthsInContext =

SUMMARIZE('Date', 'Date'[Year], 'Date'[Month Number])

// Iterate each month: get current and prior year values.

// Only include the difference when BOTH exist — this is how partial years work.

// Months with no prior year data (e.g., Jan-Aug when data starts in Sep) are skipped.

VAR _Result =

SUMX(

_MonthsInContext,

VAR _Year = 'Date'[Year]

VAR _Month = 'Date'[Month Number]

// Current year value for this specific month

VAR _CurrValue =

CALCULATE(

SELECTEDMEASURE(),

REMOVEFILTERS('Date'),

'Date'[Year] = _Year,

'Date'[Month Number] = _Month

)

// Same month, one year earlier

VAR _PriorValue =

CALCULATE(

SELECTEDMEASURE(),

REMOVEFILTERS('Date'),

'Date'[Year] = _Year - 1,

'Date'[Month Number] = _Month

)

RETURN

IF(

NOT ISBLANK(_CurrValue) && NOT ISBLANK(_PriorValue),

_CurrValue - _PriorValue,

BLANK() // Skip months without data in both years

)

)

// Guard: if there's no data at all in the current context, return BLANK

RETURN

IF(ISBLANK(_CurrentValue), BLANK(), _Result)

calculationItem 'YoY %' =

// YoY %: Percentage change vs same period last year.

// Can't just divide YoY by prior total — need filtered totals

// that only include months with data in both years.

VAR _CurrentValue = SELECTEDMEASURE()

// All Year + Month combinations in the current filter context

VAR _MonthsInContext =

SUMMARIZE('Date', 'Date'[Year], 'Date'[Month Number])

// Filtered CURRENT total: only sum months where both years have data

VAR _CurrFiltered =

SUMX(

_MonthsInContext,

VAR _Year = 'Date'[Year]

VAR _Month = 'Date'[Month Number]

VAR _CurrValue =

CALCULATE(SELECTEDMEASURE(), REMOVEFILTERS('Date'),

'Date'[Year] = _Year, 'Date'[Month Number] = _Month)

VAR _PriorValue =

CALCULATE(SELECTEDMEASURE(), REMOVEFILTERS('Date'),

'Date'[Year] = _Year - 1, 'Date'[Month Number] = _Month)

RETURN

// Only include current value when prior year also has data

IF(NOT ISBLANK(_CurrValue) && NOT ISBLANK(_PriorValue),

_CurrValue, BLANK())

)

// Filtered PRIOR total: same filter, but return the prior year value

VAR _PriorFiltered =

SUMX(

_MonthsInContext,

VAR _Year = 'Date'[Year]

VAR _Month = 'Date'[Month Number]

VAR _CurrValue =

CALCULATE(SELECTEDMEASURE(), REMOVEFILTERS('Date'),

'Date'[Year] = _Year, 'Date'[Month Number] = _Month)

VAR _PriorValue =

CALCULATE(SELECTEDMEASURE(), REMOVEFILTERS('Date'),

'Date'[Year] = _Year - 1, 'Date'[Month Number] = _Month)

RETURN

// Only include prior value when current year also has data

IF(NOT ISBLANK(_CurrValue) && NOT ISBLANK(_PriorValue),

_PriorValue, BLANK())

)

// Divide the filtered totals for an honest percentage

RETURN

IF(ISBLANK(_CurrentValue), BLANK(),

DIVIDE(_CurrFiltered - _PriorFiltered, _PriorFiltered))

formatStringDefinition = "#,0%;-#,0%;#,0%"

And there you have it! A Date table for the foundation, a calculation group for the logic, and one-liner measures that tie them together. Define it once, use it everywhere, change it in one place.