Forum Discussion

Tim_H's avatar
Tim_H
Icon for Helper I rankHelper I
5 years ago
Solved

conditional format based on calculation in matrix.

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, s...
Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    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.

MFelix's avatar
MFelix
Icon for Super User rankSuper User
5 years ago

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's avatar
Tim_H
Icon for Helper I rankHelper I
5 years ago

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