Conditionally Formatting a Date Column

[PBandI]

I am currently trying to format a date column based on if the date is in the past or if the date is blank. I am using a measure that I created for this and then passing it on to the conditional formatting for the column it should be applied to. If the date is in the past then the cell should be green, and if the date is blank then it should be red. The current measure I am using is shown below:

_HPA Status = IF(MAX('Opportunity'[HPA Signature Date]) < Today(), "Light Green" , "Light Red")

The desired outcome is to have the cell that reads 12/27/2023 as green and all the other blank cells as red. Any help would be greatly appreciated!

[vicky_ · Accepted solution]

Hi! 
The issue with your DAX measure is that any dates that are BLANK are actually evaluated as 0 (which is less than today in DAX logic), so it will always show green. To fix it, you can add another condition to check if the value is blank:

_HPA Status = IF(MAX('Opportunity'[HPA Signature Date]) < Today() && NOT(ISBLANK('Opportunity'[HPA Signature Date])), "Light Green" , "Light Red")