Forum Discussion
Conditional formatting red, yellow and green
Hello,
I need some, help please with conditional formatting. My example below is the requirement I am working on. I need to create a report to show red amber and green status against the various dates in my sample below. The challenge I am having is the courses in the table have different years to be completed. There is no start date or end, this is because new members join the team and need to complete training as they join and within the due time frame of the courses. How do I show red amber and green for when a course is over 365 days (red), less than 365 days by 30 days (amber) and with 0 to 360 days (green)? Am guessing I can use the same logic for the 2 and 3 year courses? Will I need to create a custom column for each cousrse? Any help would be very much appreciated on how I can achieve this. Thanks in advance.
| Name | Team | business continuity | bystander | fraud prevention |
| Annaually | Every 2 years | Every 3 years | ||
| Sandra Parker | Project Team | 12/01/2022 | 12/01/2022 | 28/04/2020 |
| Erica Hill | Commercial Team | 31/12/2022 | 17/03/2021 | 10/12/2019 |
| James Smith | Finance Team | 31/03/023 | 30/04/2021 | 15/03/2023 |
Hi Aya,
No worries!
So if I'm understanding correctly now (and knowing nothing else about your data model), I would do something like the following.
- Create three measures, one for each course, since they have different timelines. (Example for Business Continuity below. You will need to update the RetakeDate variable when you create the measure for the other two columns.) This measure will tell the conditional formatting in the next step what colour to highlight the cell.
Business Continuity Conditional Formatting = -- Since it must be taken within a year, EDATE takes the date and calculates 12 months in the future VAR RetakeDate = EDATE ( MIN ( Table1[Business Continuity] ), 12 ) VAR Colour = SWITCH ( TRUE(), TODAY() - RetakeDate >= 365, "Red", TODAY() - RetakeDate >= 335, "Yellow", "Green" ) RETURN Colour- Right click the field in your table/matrix, going to Conditional Formatting, then Background Colour.
- Change Format Style to Field Value, then picking the corresponding field under "What field should we base this on?"
Here is a sample file I created in case it helps. This is what my report page looks like.
Please let me know if this is not what you were looking for or if your data model doesn't quite match and you're unable to modify the measure to your needs. 😄
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)Hi Wilson,
Thank you so much! The measures work. Thanks again
7 Replies
Wilson_Memorable Member
Hello Aya,
I'm not quite following your post and had a few followup questions:
- Are the dates in the report the last time they took the course, the date on which they need to take the course again or something else?
- Your conditional formatting logic doesn't seem to be mutually exclusive. For example, it seems a course that is 345 days overdue would fall under both the green and amber conditions. Can you please double check the logic?
- Are you trying to use today (ie: whatever day the user is looking at the report) in your calculation for the conditional formatting?
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)
Hello Wilson,
Many thanks for your response and clarification questions. please see below answers to your questions:
- Yes, the dates represent when each person last completed their training. The first training is annual, the second is every 2 years and the last training is every 3 years.
- Yes this is so because each person would have completed a course at different times. So, there will be some who are still within green because they have not reached the number of days to fall within amber. Amber would indicate they need to start thinking of retaking their training before amber turns to red. I hope this makes sense?
yes, it would be whatever day the user is looking at the report
Thanks again.
- Wilson_
Hi Aya,
Thanks for your clarifications on #1 and #3.
Following up again on the conditional formatting logic, per your original post, the rules are:
"How do I show red amber and green for when a course is over 365 days (red), less than 365 days by 30 days (amber) and with 0 to 360 days (green)?"
Put a different way, the rules are:
- 365+ days overdue, red
- "less than 365 days by 30 days" (which I read to mean between 335-364 days overdue), amber
- between 0-360 days, green
There is a range of days (ie: between 335 and 360 days) that meets both the amber and green conditions. Presumably this is not supposed to be the case. Am I misunderstanding something?
- Yes, the dates represent when each person last completed their training. The first training is annual, the second is every 2 years and the last training is every 3 years.