Forum Discussion
Dates Traffic Light
Hi,
I would like a column that turns red, yellow, green if today's date is within 0-30 days, 31-45 days, 46-90 days, respectively.
See attached screenshot for reference.
Thank you,
Pablo
I corrected your measure and conditional formatting.
Please find an attachment.
Proud to be a Super User.
If I helped, please accept the solution and give kudos!
LinkedinHi PabloGiraldo ,
Here are the steps you can follow：
1. Create calculated column.
day = DATEDIFF(MAX(Submittals[Required date]),TODAY(),DAY)2. In Format, select Conditional formatting, select the field Indicator, and turn it on
3. Select Format by as Rules and Based on field as [day]
Grant conditions:
Red: 0<=day<30
Yellow: 30<=day<45
Green: 45<=day<90
Date more than 90 days to differentiate from a small green circle
4. Result.
You can downloaded PBIX file from here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi PabloGiraldo ,
Here are the steps you can follow：
1. Create calculated column.
day = DATEDIFF(MAX(Submittals[Required date]),TODAY(),DAY)2. In Format, select Conditional formatting, select the field Indicator, and turn it on
3. Select Format by as Rules and Based on field as [day]
Grant conditions:
Red: 0<=day<30
Yellow: 30<=day<45
Green: 45<=day<90
Date more than 90 days to differentiate from a small green circle
4. Result.
You can downloaded PBIX file from here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you!
I corrected your measure and conditional formatting.
Please find an attachment.
Proud to be a Super User.
If I helped, please accept the solution and give kudos!
LinkedinThanks for the help. I do have one question. Seems like you fixed part of it but seems like the formula is not working correctly. There are some dates that are past 90 days and these should be green. There are also some dates of December 2020. Shouldnt these be green instead of yellow?
Taking today as the example:
Red = 02/01/2021 - 03/03/2021 (0-30 days)
Yellow = 03/04/2021 - 03/19/2021 (31-45 days)
Green = 03/20/2021 - 5/2/2021 (46-90 days)
Thanks!
Pablo
Hi PabloGiraldo ,
You have to create a measure that will return the color code based on the Required Date. And then you can use it in the conditional formatting as a parameter.
Please share sample of data or your .pbix file and I can help you.
Proud to be a Super User.
If I helped, please accept the solution and give kudos!
Linkedinfile in the link below. The slicer i created should filter table as i slide to a certain date range (i.e. 31-45 days or so). Thank you!
