DAX to replace multiple IF statements in Excel

[MrMED87]

Hi all

could someone kindly propose a suitable DAX alternative (for PBI) to the below formula in Excel ?

=IF([@CompleteBy] = "", "NA",IF([@CompletedDate] >[@CompleteBy],"Overdue", IF(AND([@CompleteBy] < TODAY(),[@CompletedDate] =""),"Overdue","Not Overdue")))

Thanks in advance

[amitchandak]

@MrMED87 , Create a new column like

if(isblank([CompletedDate]) && [CompleteBy] <today(), "Overdue","Not Overdue")

[MrMED87]

Thanks but not quite working yet...

Overdue = IF(Table 1[Status] = "Overdue", "Overdue", IF(ISBLANK(Table 1[CompletedDate] && Table 1[CompleteBy].[Date] < TODAY()),"Overdue","Not Overdue"))

Where i have a Completeby date in the past, it is still showing as "Not Overdue"... any ideas?

thanks

[yingyinr · Accepted solution]

Hi @MrMED87 ,

You can create a calculated column as below:

Overdue = 
IF (
    ISBLANK ( 'Table'[CompleteBy] )
        && 'Table'[Status] <> "Overdue",
    BLANK (),
    IF (
        'Table'[CompletedDate] > 'Table'[CompleteBy],
        "Overdue",
        IF (
            'Table'[CompleteBy] < TODAY ()
                && ISBLANK ( 'Table'[CompletedDate] ),
            "Overdue",
            "Not Overdue"
        )
    )
)

Best Regards