Power bi month over month mom comparison without dax

Why MoM in Power BI Is More Work Than It Should Be

Month over month comparison is one of the most requested Finance and Operations metrics. It's also one of the most commonly broken ones in Power BI.

The core problem: Power BI's DAX time intelligence functions require a properly structured date table — a continuous, unbroken sequence of dates with no gaps — connected to your fact table. When that setup is off, MoM measures return blank or wrong values across entire months. Most debugging time goes into the date table, not the measure itself.

This article covers three approaches — from standard DAX to no-DAX at all — so you can pick the one that fits your situation.

Before You Write Any DAX: Check Your Date Table

Two checks that prevent most issues:

• Continuous date range: Your date table must contain every calendar day from the earliest to the latest date in your fact table — no gaps. If January has 31 days but your date table only has transaction days, DATEADD will return blank for days with no data.
• Mark as date table: In Power BI Desktop, right-click your date table → Mark as date table → select the date column. This disables the auto date hierarchy and tells DAX time intelligence which column to use.

Once those two are confirmed, any of the three methods below will work reliably.

Method 1: DATEADD (Standard, Most Flexible)

DATEADD shifts the date context by a specified interval. It works with any granularity — day, month, quarter, year — making it the most reusable pattern for time comparisons.

Sales MoM % =
VAR _curr = [Total Sales]
VAR _prev = CALCULATE(
    [Total Sales],
    DATEADD('Date'[Date], -1, MONTH)
)
RETURN
    IF(
        ISBLANK(_prev) || _prev = 0,
        BLANK(),
        DIVIDE(_curr - _prev, ABS(_prev))
    )

Use ABS(_prev) in the denominator to handle sign correctly when the prior month was negative (e.g. a loss).

To show absolute MoM change (not percentage):

Sales MoM Δ =
VAR _curr = [Total Sales]
VAR _prev = CALCULATE(
    [Total Sales],
    DATEADD('Date'[Date], -1, MONTH)
)
RETURN IF(ISBLANK(_prev), BLANK(), _curr - _prev)

Limitation: You need to duplicate this pattern for every metric. Five metrics = ten measures (one for value, one for %). When Finance adds a new KPI, you go back to Desktop to add two more measures.

Method 2: PREVIOUSMONTH (Simpler, Less Flexible)

PREVIOUSMONTH returns the full prior calendar month. Cleaner to write, but less flexible — it always returns a complete month, which breaks Month-to-Date comparisons.

Sales Prev Month =
CALCULATE(
    [Total Sales],
    PREVIOUSMONTH('Date'[Date])
)

Sales MoM % =
DIVIDE(
    [Total Sales] - [Sales Prev Month],
    ABS([Sales Prev Month])
)

Use this when you always compare full months. Avoid it for MTD vs LMTD comparisons — use DATEADD instead.

Common issue with PREVIOUSMONTH: If your visual shows a blank in the last column of a matrix, it's usually because the current incomplete month has no "previous" to compare to at the total level. Fix by adding IF(ISBLANK([Sales Prev Month]), BLANK(), ...) around the calculation.

DATEADD vs PREVIOUSMONTH: When to Use Which

The Problem That Gets Worse Over Time

Both DAX methods work. The issue isn't correctness — it's maintenance at scale.

This is the pattern most Power BI developers recognize: MoM DAX starts simple and becomes a maintenance burden as the report grows. See the full breakdown in Variance Analysis in Power BI Without DAX.

Method 3: No DAX — Built-In MoM in the Published Report

If the goal is to give Finance teams MoM variance they can use and modify themselves — without going back to Desktop for every new metric — Flexa Tables handles it as a built-in feature.

How it works:

1. Install Flexa Tables from Microsoft AppSource (free trial, under 5 minutes)
2. Add to your existing report canvas — no changes to your data model
3. Publish to Power BI Service
4. End-users click the variance button, select two months to compare, MoM column appears instantly — absolute value and % change, color-coded

No DAX measures, no Desktop access needed, no developer in the loop for every new comparison request. Works for DoD and YoY with the same interface.

Which Method to Use

Frequently Asked Questions