Forum Discussion

lparks's avatar
lparks
Regular Visitor
4 years ago
Solved

Month Over Month % Change Formula

Hello,    I'm new to power bi and need help with a formula for percent change MOM, please. I would like the percent change as a percentage, but below is the formula of what I want. I also included ...
Need Help
Show and Tell
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi lparks ，

    According to your description, here's my solution.

    1.Create a calculated column to calculate the sum total clicks for each month.

    SUM Total Clicks =
SUMX (
    FILTER ( 'Table', 'Table'[Month] = EARLIER ( 'Table'[Month] ) ),
    'Table'[Total Clicks]
)

    2.Create a measure.

    SUM Total Clicks MOM% =
VAR _Previous =
    MAXX (
        FILTER ( ALL ( 'Table' ), 'Table'[Month] = MAX ( 'Table'[Month] ) - 1 ),
        'Table'[SUM Total Clicks]
    )
VAR _DIFF =
    MAX ( 'Table'[SUM Total Clicks] ) - _Previous
RETURN
    DIVIDE ( _DIFF, _Previous )

    3.Get the expected result, as your snapshot shows part of the data, I get a great value.

    I attach my sample below for reference.

    Best Regards,
    Community Support Team _ kalyj

     

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

     

DavidAnthony's avatar
DavidAnthony
Helper II
26 days ago

Adding to the accepted solution  worth noting one limitation of the MAXX(FILTER(...)) approach: it works when Month is stored as an integer (1, 2, 3...) but breaks at year boundaries (December vs January) because MAX(Month) - 1 for January returns 0, not December of the previous year.

For a more robust MoM % that handles year boundaries correctly, use a proper date table with DATEADD:

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

Format the measure as Percentage in the column formatting panel  no need to multiply by 100.

For anyone who needs MoM % change displayed directly in a published Power BI report without writing or maintaining DAX measures  Flexa Tables on AppSource adds MoM, YoY, DoD, and YTD variance as built-in columns that Finance or Marketing users can toggle themselves after publishing. No Desktop access needed.
https://marketplace.microsoft.com/en-us/product/devspearllc1670524393721.flexa_tables?tab=Overview