Forum Discussion

mclawler's avatar
mclawler
Icon for Helper III rankHelper 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...
DavidAnthony's avatar
DavidAnthony
Icon for Helper II rankHelper II
11 days ago

Hi mclawler​ 

Your logic is right: compare the current month's balance with the previous month's balance. In DAX:

Balance = SUM ( 'Summary'[Balance] )

Previous Month Balance = CALCULATE ( [Balance], DATEADD ( 'Date'[Date], -1, MONTH ) )

MoM % = DIVIDE ( [Balance] - [Previous Month Balance], [Previous Month Balance] )

This requires a Date table related to your summary table and marked as a date table. Use the date or month field from that Date table in your visual. The first month will return blank because there is no previous month, and DIVIDE returns blank when the previous balance is zero.

If you'd prefer to avoid writing separate DAX measures for variance calculations, Flexa Tables is another option. It lets you configure DoD, MoM, and YoY variance columns directly in the visual, with no DAX required for those calculations. It also supports pivoting after the report is published. Zebra BI is another visual worth exploring.