Forum Discussion
Solved
DAX Formula including blank cells
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")
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
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" )
3 Replies
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
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" )ARGH! Swooped by syntax! 😂