topic Re: MoM % Variance in DAX Commands and Tips

MoM % Variance

mclawler — Thu, 09 Feb 2023 00:27:43 GMT

Hi Everybody, first post, I am a rookie at this stuff and for the life of me can't seem to figure out the correct DAX for MoM % Variance. I'm self-taught and done pretty well so far, but I must be missing something simple and making a rookie mistake on this one. All I am trying to achieve, is to show how much the Balance went up(or down) from one month to the next. I'm sure I am way over-thinking it, logic tells me I need to use the sum of Balance filtered by the Month(date) and divide that by the sum of balance for previous month. Please advise, and thank you this forum has been by far my most valuable resource thus far.

I have more detailed tables as well, but for this specific visual I am literally only using this table I created for summary and simplicity

Re: MoM % Variance

wdx223_Daniel — Thu, 09 Feb 2023 02:55:09 GMT

Re: MoM % Variance

Anonymous — Thu, 09 Feb 2023 03:25:11 GMT

Hi mclawler

You can reger to the following measure

MTM% = var a=SUMX(FILTER(ALLSELECTED('Table'),EOMONTH('Table'[Snapshot month],0)=EOMONTH(MAX('Table'[Snapshot month]),1)),[Exposure Balance]) return DIVIDE(a-SUM('Table'[Exposure Balance]),SUM('Table'[Exposure Balance]))

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: MoM % Variance

mclawler — Thu, 09 Feb 2023 16:24:28 GMT

The results you got are what I'm after! But when I unput the DAX 2 different ways I get 2 different errors, please advise what I'm doing incorrectly:

Thank you!

Re: MoM % Variance

mclawler — Thu, 09 Feb 2023 16:30:06 GMT

Your DAX did exactly as your chart shows, however I don't think it's giving me the results I'm looking for.

For example -

from 2/1/2022 to 3/1/2022 I'm looking for a -50%

from 3/1/2022 to 4/1/2022 I'm looking for 39.6%

from 4/1/2022 to 5/1/2022 I'm looking for 0%

from 5/1/2022 to 6/1/2022 I'm looking for 497%

Current month divided by previous month for the increase/decrease %

Thank you!

Re: MoM % Variance

mclawler — Thu, 09 Feb 2023 21:43:27 GMT

Figured it out with the builtin Quick Measure suggestions, I thought since it was showing me an error it wasn't working, but the calculations appear to be correct regardless of the error. Thanks everyone

Exposure Balance MoM% =

IF(

ISFILTERED('Exposure Balance Monthly'[Snapshot Month]),

ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

VAR __PREV_MONTH =

CALCULATE(

SUM('Exposure Balance Monthly'[Exposure Balance]),

DATEADD('Exposure Balance Monthly'[Snapshot Month].[Date], -1, MONTH)

)

RETURN

DIVIDE(

SUM('Exposure Balance Monthly'[Exposure Balance]) - __PREV_MONTH,

__PREV_MONTH

)

Re: MoM % Variance

PBIdashboards — Thu, 18 Jun 2026 03:55:49 GMT

The most common reason MoM % breaks for self-taught users: DATEADD needs a proper Date table marked as Date Table in the model. If you're using a date column directly from your fact table without a separate Date table, DATEADD returns BLANK.

The clean pattern:

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

Two things to check if still getting wrong results: (1) your Date table is marked as a Date Table (right-click table → Mark as date table), and (2) you're filtering by Month in the visual, not Day DATEADD with MONTH needs month-level granularity to work correctly.

For anyone who needs MoM variance in a published report where Finance users can add or switch periods themselves without going back to Desktop, Flexa Tables on AppSource handles MoM as a built-in button no DAX required

Re: MoM % Variance

mizan2390 — Fri, 19 Jun 2026 17:05:52 GMT

hi mclawler

As a DAX practitioner, I have to share a crucial best practice: DAX time intelligence functions behave unpredictably if you do not have a dedicated Date Table. Even if you built a simple summary table for your balance data, I highly recommend creating a separate table just for your dates (a continuous calendar), marking it as a "Date Table" in Power BI, and linking its Date column to your summary table's Date column. This ensures your "Previous Month" calculations never skip a beat.

That built-in Quick Measure you found is actually hardcoded to throw an error under certain conditions. Notice this part of the code:

IF(
ISFILTERED('Exposure Balance Monthly'[Snapshot Month]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column.")

Microsoft writes their Quick Measures defensively. It is literally programmed to break and show that text if you don't use it exactly with the built-in date hierarchy. It's clunky, hard to read, and not how you want to write DAX long-term.

Now, next looking at the two screenshots you provided, you are making a very common mistake: referencing the wrong table name.

First Error Screenshot: You copied SUM(Table[Exposure Balance]). Power BI is telling you "Cannot find table 'Table'" because your table is not actually named "Table".
Second Error Screenshot: You tried to fix it by writing SUM('Exposure Balance'[Exposure Balance]). Here, Power BI says "Cannot find table 'Exposure Balance'". That is because "Exposure Balance" is the name of your column, not your table.
Based on the Quick Measure code you posted earlier, your actual table name is 'Exposure Balance Monthly'
Please try this DAX:
```
MoM % Variance = 
VAR CurrentBalance = SUM ( 'Exposure Balance Monthly'[Exposure Balance] )
VAR PreviousBalance = 
CALCULATE (
SUM ( 'Exposure Balance Monthly'[Exposure Balance] ),
PREVIOUSMONTH ( 'Exposure Balance Monthly'[Snapshot Month] )
)
RETURN
DIVIDE (
CurrentBalance - PreviousBalance,
PreviousBalance
)
```

Re: MoM % Variance

Divyaraj_Rathod — Wed, 29 Jul 2026 13:57:54 GMT

Your logic is actually correct, the issue is more likely how the comparison is being done. Since your "Exposure Balance Monthly" table is a snapshot table (one row per month-end, not additive daily transactions) and probably isn't hooked up to a full marked Date table with contiguous dates, relying on functions like DATEADD or PREVIOUSMONTH here often returns blank because those need a real calendar table. Since your data already has one row per month, it's more reliable to compare explicit Snapshot Month values directly:

MoM % Variance =

VAR CurBal = SUM('Exposure Balance Monthly'[Exposure Balance])

VAR CurMonth = MAX('Exposure Balance Monthly'[Snapshot Month])

VAR PrevMonth = EOMONTH(CurMonth, -2) + 1

VAR PrevBal =

CALCULATE(

SUM('Exposure Balance Monthly'[Exposure Balance]),

ALL('Exposure Balance Monthly'),

'Exposure Balance Monthly'[Snapshot Month] = PrevMonth

)

RETURN

DIVIDE(CurBal - PrevBal, PrevBal)

EOMONTH(CurMonth, -2) + 1 gets you the first day of the prior month regardless of which day-of-month your Snapshot Month values use. The ALL() clears the existing month filter from the visual so we can manually re-filter to exactly that one previous month. This avoids needing a full calendar/date table just to get a working MoM% for a monthly snapshot metric like this.