topic And, DateDiff in DAX Commands and Tips

And, DateDiff

PC2022 — Mon, 04 Nov 2024 20:36:52 GMT

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" ))

Re: And, DateDiff

FreemanZ — Mon, 04 Nov 2024 21:16:21 GMT

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

Re: And, DateDiff

PC2022 — Tue, 05 Nov 2024 13:40:17 GMT

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.

Re: And, DateDiff

PC2022 — Tue, 05 Nov 2024 13:52:51 GMT

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

Re: And, DateDiff

FreemanZ — Tue, 05 Nov 2024 13:58:23 GMT

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" )

Re: And, DateDiff

PC2022 — Tue, 05 Nov 2024 14:50:50 GMT

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