Conditional Formatting Matrix Based on Date Comparisons

[SteWilf]

Hi, 

I hope everyone is well, and hoping someone can help solve a problem a few of us have been trying to solve and so far been unable. We've spent a lot of time on this, used many articles from these pages and been able to get part way there but really struggling with the last part, colour coding the summed zero values in the matrix based upon the month and when they occur compared to a pair of activity dates. I have attached some simplified sample files in a link below of what we are trying to achieve. The real model is way more complex than this sample but trying to boil it down to where we are struggling.

We are trying to use a Matrix chart to show and colour code the occurence of failures based upon failure dates, containment dates and resolution dates as well as months where there are no failures.

Overall, the requirements are:

• If a failure mode has no contain or resolution date the background colour should be white.
• If a failure mode has a contain and/or resolution date then:
  1. Failures before the both the Contain and Resolution Date background colour should be white.
  2. If a failure occurs after a Contain date but before a Resolution date then the background colour should be yellow.
  3. If a month has no failures after the Contain date but before the Resolution date the background should be blue.
  4. If a failure occurs on or after a Resolution date the background colour should be red
  5. If a month after a Resolution date has no failures background should be green.

In the attached files we have used a calculated column in the 'Sheet1' table called "CF-Cell" to calculate the colour for the specific cell element for the given item combination based upon its date of failure compared to the Contain and resolution dates.

This value is then used in a measure called "CF Test" which we are using to colour code the elements. To try and solve the issue with the cell elements with a 0 value we created a measure in the Query1 Table which is a dates table to count all elements and use the ones that were 0 to colour code but we haven't been able to work out how to compare the value and the month in which it occurs to the Contain or Resolution dates, using row wise calculations we keep running into different issues such as inability to use boolean comparators with a MINX function etc and now are really stuck.

Here is where we have been able to get using a combination of measures and calculated values within the base tables.

 But this is what the output should look like.

I have attached the sample pbix, excel file for the data and the other items at the following link:

Link to Files in Google Drive 

Would really appreacite any help!!!

Many thanks in advance,

Steve

[SteWilf · Accepted solution]

 To close the loop on this, I was able to solve the problem through the use of several measures and a complete redesign of the data model.

[Anonymous]

Hi  @SteWilf ,

I think it's feasible to create a few more Measures or use variables from multiple Var's in one Measure.

So have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

Yilong Zhou

[Anonymous]

Hi @SteWilf ,

I opened the .pbix file and find that you can change the CF Test codes.

You can change the IF ([CellCount] = 0,"Blue", it makes all the zeros blue, which is not satisfying your needs, so I think you can make changes.

Best Regards

Yilong Zhou

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

[SteWilf]

Hi @Anonymous 

Thank you for taking a look at the files and sharing feedback.

The IF ([CellCount]) = 0, "Blue" is as close as we have been able to get to the requirement, this is where we are struggling to solve the problem because we can't find a way to compare the position of that 0 value in the matrix to the dates of relevance. i.e.  if the zero count is in the August column, we need to be able to say is August greater than, less than or equal to the Contain Date and/Resolution Resolution Date for the row, but so far have been unable to find a way to do this. 

Are you aware of any way this can be done? 

Thank you

Steve