Forum Discussion
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...
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