conditional format based on calculation in matrix.

[Tim_H]

Hi

I want to add an arrow on below matrix for each month compared with the previous month.

When critical tickets increase, stay the same or get lower

When Major tickets in crease, stay the same or get lower

if possible in the end should look something like below (arrow can be extra column for each month)

Thanks

[MFelix · Accepted solution]

Hi @Tim_H ,

Create the following measure:

Previous_Month_Values = COUNT('Table'[Severity]) - CALCULATE(COUNT('Table'[Severity]); DATEADD('Table'[Date];-1;MONTH))

Now create the condittional formatting based on this measure:

Check PBIX file attach is using a sum and not a count but works in the same way just replace use the syntax above.

[Tim_H]

Hi @MFelix 

Thanks, but not 100% working. I do see arrows but all have same color. I think this is because my 'severity' is not counted as you have in column Quantity but is still line/line for several dates in one month.

Below extract from very uge file ( + 18K tickets)

kr

Tim

[MFelix]

Hi @Tim_H,

A good practice for this type of calculation is to add a Calendar table that related with your date column then you just need to do the following measure for the formatting:

Previous_Month_Values = 
COUNT('Table'[Severity]) - CALCULATE(COUNT('Table'[Severity]); DATEADD('calendar'[Date];-1;MONTH))

Now do the formatting has previously.

[Tim_H]

Hi @MFelix 

Works ! great thanks...

had to change my formula by adding .date

DATEADD(overview[Reported date YYYY-MM].[Date],-1,MONTH))

Previous_Month_Values = count(overview[Severity]) - CALCULATE(COUNT(overview[Severity]),DATEADD(overview[Reported date YYYY-MM].[Date],-1,MONTH))

Kind regards

Tim