Conditional formatting red, yellow and green

[aya80]

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

[Wilson_ · Accepted solution]

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?)

[Wilson_]

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?)

[aya80]

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?

[aya80]

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

[Wilson_ · Accepted solution]

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?)

[aya80 · Accepted solution]

Hi Wilson,

Thank you so much! The measures work. Thanks again

[Wilson_]

Hey Aya,

Awesome, glad I could help. 😄

Could you please do me a favour and mark my last response as the solution to "close" this post and move that comment to the top in case other people with the same issue find this thread? I'd appreciate it.