Month Over Month % Change Formula

[lparks]

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 a table of what the data look like to help. Can someone help, please

Total current month clicks - total previous month clicks / total previous month * 100 / 1

[v-yanjiang-msft · Accepted solution]

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.

[lparks]

Hi @amitchandak thank you so much. Do I need to put all of the info in one formula?

[amitchandak]

@lparks , You can create this month vs last month measure using date table 

MTD Sales = CALCULATE(SUM(Table[Total click]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Table[Total click]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

last month Sales = CALCULATE(SUM(Table[Total click]),previousmonth('Date'[Date]))
next month Sales = CALCULATE(SUM(Table[Total click]),nextmonth('Date'[Date]))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA