Dates Traffic Light

[PabloGiraldo]

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

[lkalawski · Accepted solution]

@PabloGiraldo 

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!  Linkedin

[Anonymous · Accepted solution]

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.

[PabloGiraldo]

Thank you!

[lkalawski · Accepted solution]

@PabloGiraldo 

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!  Linkedin

[PabloGiraldo]

Thanks 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

[lkalawski]

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!  Linkedin

[PabloGiraldo]

file 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!

https://walshgroup-my.sharepoint.com/:u:/p/pgiraldoclavijo/EflsSIXQn5lNqMJUZ_y1lIEBalfwbhFiNq6WYXX32...