Conditional Formatting to Conditional Column if Null Field

[BulldogTom]

Hello! I would like to apply conditional formatting to a conditional column I created in Power BI Desktop but I'm stumped. I have a table with four columns.  The RTA_Current column is conditional and fills in if there is a date in RTA_Actual. The logic I'd like to apply is:

If RTA_Actual is not null 

Change RTA_Current background color to gray

If RTA_Actual is null

Change RTA_Current background color to yellow if < 30 days from today

Change RTA_Current background color to red if < Today

Any ideas?

[Anonymous · Accepted solution]

Hi  @BulldogTom ,

Here are the steps you can follow：

1. Create measure.

Color =
var _datediff=DATEDIFF(MAX('Table'[RTA Current]),TODAY(),DAY)
return
IF(
    MAX('Table'[RTA Actual])<>BLANK(),"gray",
    IF(
        MAX('Table'[RTA Actual])=BLANK()&&_datediff<30,"yellow",
        IF(
         MAX('Table'[RTA Actual])=BLANK()&&MAX('Table'[RTA Current])<TODAY(),"red"  
)))

2. [[RTA Current] – Conditional formatting – Background color.

3. Result:

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

[BulldogTom]

Great work!

[vanessafvg]

you could do it in a switch statement, and do some precalculations with the dates in a variable potentially to work out the time differences

https://learn.microsoft.com/en-us/dax/switch-function-dax

https://learn.microsoft.com/en-us/dax/datediff-function-dax

are you able to share your data in text format?

[BulldogTom]

Thanks for taking a look.  I'll read up on these options.  Here is a data excerpt:

Project	RTA	RTA Actual
B591 breakroom HVAC repairs	12/01/2022	01/18/2023
DDRT Add B811 to the DDRT FIS Contract	01/22/2024
DDRT B499A and B499B Fire Pump Repairs	10/27/2022	05/15/2023
DDRT B581 Restroom Partition Renovation	12/14/2022	12/14/2022
DDRT Renovation Building 552 Zone One Restrooms	03/31/2022	03/30/2022
DDRT Replace Carpet in B500	01/16/2024
DDWG Bldg. 368 Overhang Bay E South West	06/30/2023
DDRT B499 East Dock Canopy Replacement	1/22/2024
DDWG Bldg. 660 Damaged Fire Riser Repair	10/19/2022	10/19/2022
DDRT Inst Big Fans in B499 Z2	02/12/2024
DDNV Replace Dry Fire System Compressors	03/15/2024