Conditional formatting dates values

[jalaomar]

Hello,

Hope you can support with the following question. I have a Planned dates field and currently applied the following conditional formatting but would like to add some more to it.

1. How can I ensure that the blank rows are not highlighted?

2. is it possible to apply conditional formatting based on days to completion? meaning if it's 7 days left to planned date then show yellow, if it's passed planned date then show red and if it's more than 7 days left then show green?

2. How can I adjust the font color? if red, then font color should be white, yellow & green = font color should be black

Color Date = if(FIRSTNONBLANK('QA Plan'[Planned_Completion_Date],TODAY()) <today(),"red","lightgreen")

Thanks!

[v-xiaotang · Accepted solution]

Hi @jalaomar 

Thanks for reaching out to us.

you can try this,  create the measures below,

Measure1 = 
var _t1= MIN('Table'[Planned Date])
var _diff=DATEDIFF(TODAY(),_t1,DAY)
return SWITCH(TRUE(),
ISBLANK(_t1),"white",
_diff<0,"red",
_diff<=7,"yellow",
_diff>7,"green")

Measure2 = IF([Measure1]="red","white")

result

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@jalaomar , Try like

Color Date = if(Max('QA Plan'[Planned_Completion_Date]) <today(),"red","lightgreen")