How to calculate a measure referred to the previous month

[mtrevisiol]

Hi everyone.

I've got a matrix which shows the margin for each month of 2018, 2019, 2020, 2021:

Since I want to use the conditional formatting, I would like to compare the margins from one month to another in order to apply different colours to the cells:

• if the margin of this month is higher than the margin of the previous month --> green
• if the margin of this month is lower than the margin of the previous month --> red

Margins are calculated using a different measure for each year (only the year value changes):

Margin 2021 = CALCULATE(

         SUM('Sales'[Value]) - SUM('Sales'[Total Cost]),2),

         VALUE('Sales'[DATA_doc_YY])=YEAR(NOW()),

         'Sales'[DataType] = "Billed")

Thank you for the help!

[V-lianl-msft · Accepted solution]

Hi @mtrevisiol ,

You could refer to this blog to create a measure of the previous month.

https://www.sqlbi.com/articles/comparing-with-previous-selected-time-period-in-dax/ 

Then create a condition measure to set the background.

condition = IF(Sales[Sales PM]>Sales[Sales Amount],"red","green")

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

[CNENFRNL]

Author another measure to calculate MoM margin difference to be used in the "conditional formatting" functionality.