Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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.
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
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?)
Hello Aya,
I'm not quite following your post and had a few followup questions:
----------------------------------
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, it would be whatever day the user is looking at the report
Thanks again.
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:
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
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.
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
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
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.
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
62 |