MoM % Variance

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

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.

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.

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

        )

)

 

 

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
  )