Show values as % difference of previous month

[ContabilidadBI]

Hello,

I would like to do something that is really easy to do in excel pivot tables but I can't figure out how to do it in power BI. If we have a matrix with months in the colums and clients in the rows, how can I make that the values of the matrix are the % difference with the previous month instead of the actual sales number of the month?

Sorry if its a very simple question, I am new to Power BI and DAX, and almost new to Data Analysis.

Thank you very much

[Anonymous]

Hi @ContabilidadBI

If you are looking for something like this

then you need at least a calendar table and a table with all orders.

Further more you will need measures, written in DAX.

Sales = CALCULATE(sum(Orders[Value]))

Saleslastmonth = CALCULATE(SUM(Orders[Value]); PARALLELPERIOD(Calendar[Date];-1; MONTH))

Sales = CALCULATE(sum(Orders[Value]))

For demo purposes I've used three measure. You can accomplish the task with just one measure.

Hope this helps

[ContabilidadBI]

Hi @Anonymous,

Thanks for your answer,

I have a date table and I know the basics of DAX. What I would want is the % increase or decrease in the sales amount for that client, something like this but in power BI. So the measure has to be something like divide(sales this month-sales last month) ; sales last month).

Thanks for your help

[v-huizhn-msft]

Hi @ContabilidadBI,

Just create measure as @Sean posted, and create matrix visual, select clients as X axis, and month as column level, the measure as value level. If it is not what you want, please post your sample data and list expected result for further analysis. 

Best Regards,
Angelia

[ContabilidadBI]

Hi guys,

I have created the measure:

But the result is not what I was expecting:

Am I doing something wrong? It's just VALUE-VALUE LAST MONTH / VALUE LAST MONTH. I don't know why this measure is not working for me.

Thank you for the help guys.

[ContabilidadBI]

Sorry guys,

It's working perfectly, I just needed to visualize it in %.

Is it possible to add conditional formatting so positive % will show as green and negative % in red? I can't see that option, only to put lowest value (color) highest value (color)

Thanks you so much!!!

[v-huizhn-msft · Accepted solution]

Hi @ContabilidadBI,

We are unable to add condition positive % will show as green and negative % in red. But you can right click your measure->conditional formatting, then select dilverging. You can type 0 as number in center, and type you min negative as min number, and max positive as max number.


You will get the following screenshot. All the negative values are shown in Red, and all positive values are shown in Green. But the color changes gradually according to the size of number.

If you have any question, please let me know.

Best Regards,
Angelia

[Sean]

MoM % Change MEASURE =
DIVIDE (
    SUM ( Table[Value] )
        - CALCULATE ( SUM ( Table[Value] ), PREVIOUSMONTH ( 'Calendar'[Date] ) ),
    CALCULATE ( SUM ( Table[Value] ), PREVIOUSMONTH ( 'Calendar'[Date] ) ),
    0
)

It would be better if you break this Measure apart into separate Measures

So you have Total, Total Previous Month, Month on Month $ amount change and Month on Month % Change

But the above Measure should give you the Month on Month % Change in the setup you've shown!

Hope this helps!