Forum Discussion

mclawler's avatar
mclawler
Helper III
3 years ago

MoM % Variance

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 m...
mizan2390's avatar
mizan2390
Super User
2 months ago

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
    )