Forum Discussion

Tim_H's avatar
Tim_H
Helper 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
Super User
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.

  • Tim_H's avatar
    Tim_H
    Helper I
    5 years ago

    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's avatar
      MFelix
      Super 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
        Helper 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