DAX Formula including blank cells

[Buyer_JC78]

I'm looking for some help to return a "Yes", "No" value in PowerBI. I need to identify if my payment date is greater than 60 days (Yes). However, some of the cells are blank and the formula I'm using below is coming up with errors. Is there a better formula or a way to deal with the blank cells so that they return as "Yes", i.e. greater than 60 days please?

>60 Days Overdue = IF(OR(AND([Paid Date]-[Invoice Date],[No. of Days to Pay]>=60), AND(BLANK ([Paid Date])), "Yes", "No")

[BA_Pete · Accepted solution]

Hi @Buyer_JC78 ,

Try this measure:

>60 Days Overdue =
IF(
	(NOT ISBLANK([Paid Date]) && [Paid Date] - [Invoice Date] >= 60)
	|| (ISBLANK([Paid Date]) && TODAY() - [Invoice Date] >= 60),
	"Yes",
	"No"
)

Pete

[BA_Pete · Accepted solution]

Hi @Buyer_JC78 ,

Try this measure:

>60 Days Overdue =
IF(
	(NOT ISBLANK([Paid Date]) && [Paid Date] - [Invoice Date] >= 60)
	|| (ISBLANK([Paid Date]) && TODAY() - [Invoice Date] >= 60),
	"Yes",
	"No"
)

Pete

[AlexisOlson · Accepted solution]

This might be slightly more reader-friendly:

>60 Days Overdue = 
VAR _Date = IF ( ISBLANK ( [Paid Date] ), TODAY(), [Paid Date] )
RETURN
    IF ( _Date - [Invoice Date] >= 60, "Yes", "No" )

[BA_Pete]

ARGH! Swooped by syntax! 😂