Month On Month mvmt % calculating incorrectly when prior month a negative value

[UK06B1]

Hello

Can anyone help – using the month on month movement in Power BI I don’t get the desired result in the %s when there is a credit value in the previous month. Example below

The costs increased from a credit of 72k to a credit of 33k in the current month  - there for its an increase in the over all costs of 38k (as shown in the abs calc ), but the % shows a negative 54% - if I were to do in excel I use the formula as shown in the 2nd screen shot – how can I replicate this in the BI calc, I have tried a few ways but am struggling to make it work .

Once i get this correct i can then conditional format correctly on the % as currently its not working given the % movment is wrong sign convention. 

Thanks

[UK06B1]

Great thanks alot for your help that works. 

[audreygerred]

You are very welcome! Happy to help!

[audreygerred · Accepted solution]

Hi! You can do another measure to account for what you need. I put the following data in a table:

I then made a measure for CY = 

SUM('Table'[Cost])

Then, one for PY =

Cost PY = CALCULATE([Cost CY], SAMEPERIODLASTYEAR('DateTable'[Date]))

Then, one for the difference = 

[Cost CY]-[Cost PY]

Then one for YoY% (can be done for MoM as well, but I went with YoY since your data referenced CY and PY) = 

Cost CY YoY% =

IF(

    ISFILTERED('Table'[Date]),

    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),

    VAR __PREV_YEAR = CALCULATE([Cost CY], DATEADD('DateTable'[Date], -1, YEAR))

    RETURN

        DIVIDE([Cost CY] - __PREV_YEAR, __PREV_YEAR)

)

Then finally - the one that accounts for the negative in PY = 

IF([Cost PY]>0,[Cost CY YoY%],(-1*DIVIDE([Diff],[Cost PY])))

And, here are the results: