And, DateDiff

[PC2022]

I need to determine which Expiration Date is 30 days out and highlight it. I was trying to write the formula below and then use "1", "0" in the conditional formatting. My formula doesn't work. Can anyone help please?

ApproachingDate = IF

(( AND ( NOT ISBLANK (( [Expiration Date] )), DATEDIFF (TODAY (),crcc5_permitcoordination[Expiration Date], DAY ) = 30, "1", "0" ))

[FreemanZ]

hi @PC2022 ,

try to apply the following measure as the conditional background color to  [Expiration Date] field of your visual:

ApproachingDate = IF

( AND ( NOT ISBLANK (( [Expiration Date] )), DATEDIFF (TODAY (),crcc5_permitcoordination[Expiration Date], DAY ) = 30, "red" )

like this:

 https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-conditional-table-formatting

[PC2022]

I changed DAX and it gave me no errors:

ApproachingDate = IF

 ( NOT ISBLANK (( crcc5_permitcoordination[Expiration Date] )) || DATEDIFF (TODAY (),crcc5_permitcoordination[Expiration Date], DAY ) = 180, "red" )

But when i applied conditional formatting it highlighted all rows except for blanks

[FreemanZ · Accepted solution]

hi @PC2022 ,

depending on your business logic, you need to change it to:

ApproachingDate = IF

 ( NOT ISBLANK (( crcc5_permitcoordination[Expiration Date] )) && DATEDIFF (TODAY (),crcc5_permitcoordination[Expiration Date], DAY ) >=30, "red" )

[PC2022]

I added one more && and it worked! Thank you!

[PC2022]

DAX didn't work, gave this error: too many arguements were passed to the AND function, The max argument count for the function is 2.