Forum Discussion
Conditional formatting red, yellow and green
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
Memorable MemberHello 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?
Hi Wilson
Thank you for your response.
-yes, 365+ days overdue, red- you are correct 335-364 days over should be amber
- and between 0-334 gays should be green
I am hoping this resolves the overlap of amber and green conditions?
Thank you so much for your patience, I am new to Power BI and still trying to wrap my head round a lot of things. The idea is in my head, but it appears am having difficulties putting into writing. I do hope the above makes sense?
Thnak you
- 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.