How to convert excel formula to M (or Dax)?

[bcbuckley13]

Does anyone know how I would write this in M (or dax)?

IF(AND([Course Complete Date] <>"",[Course Complete Date]>[Due Date],"No",
IF(AND([Course Complete Date]<>"",[Course Complete Date]<[Due Date],"Yes",
IF(AND(ISBLANK([Course Complete Date],[Due Date]<TODAY()),"Overdue","Pending")))

[edhans]

Try this:

if [Course Complete Date] <>"" and [Course Complete Date]>[Due Date] then "No"
else if [Course Complete Date]<>"" and [Course Complete Date]<[Due Date] then "Yes"
else if [Course Complete Date] = null and [Due Date]<DateTime.LocalNow() then "Overdue"
else "Pending"

[bcbuckley13]

@edhans  Now I'm back to getting an error.

[edhans]

CLick on the white space next to the word "error" and tell us what the error is. It will show up in yellow at the bottom.

If you could provide sample data that would really help as we are all just guessing. Screenshots are fine for expected results, but pretty useless for actually examining and testing data.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[edhans · Accepted solution]

Ok, try this, but this still isn't right.

                if [Course Complete Date] <> null then
                    if [Course Complete Date] > [Reg Date] then "No"
                    else if [Course Complete Date] < [Reg Date] then "Yes" else null
                else if [Course Complete Date] = null then
                    if [Reg Date] < DateTime.Date(DateTime.LocalNow()) then "Overdue" else null
                else "Pending"

The problem is you have some dates where the reg date and completed date are the same. Are you wanting all of those to be pending? If so, change to :

                if [Course Complete Date] <> null then
                    if [Course Complete Date] > [Reg Date] then "No"
                    else if [Course Complete Date] < [Reg Date] then "Yes" else "Pending"
                else if [Course Complete Date] = null then
                    if [Reg Date] < DateTime.Date(DateTime.LocalNow()) then "Overdue" else "Pending"
                else "Pending"

 The issue with the error seems to be you cannot test for null and another condition with an and operator, so I needed to split the logic into even more nested if statements.

[bcbuckley13]

Thank you to everyone that help solve this!!! Such an amazing community!

@edhans @aj1973 @Greg_Deckler @BA_Pete 

[edhans]

glad to have assisted with your project @bcbuckley13 !

[bcbuckley13]

@edhans 

Does this work? I've never shared a file here.

https://drive.google.com/file/d/1iCbcxGV14Az3xAWVUV4BgLHaJ6W5dsuC/view?usp=sharing

[Greg_Deckler]

That code should work in DAX with minimal changes although I would recommend using SWITCH(TRUE()...) instead of nested IF statements.

M code is going to be something like:

if [Course Complete Date] <>"" and [Course Complete Date]>[Due Date] then "No" else if [Course Complete Date]<>"" and [Course Complete Date]<[Due Date] then "Yes" else if [Course Complete Date] <>"" and [Due Date] < DateTime.LocalNow() then "Overdue" else "Pending"

[bcbuckley13]

@Greg_Deckler  Thank you. However, I got the dredded Null error from the blank Course Completed date column.

[BA_Pete]

Hi @bcbuckley13 ,

Use Greg's code, but instead of using <>"" use <> null

Pete

[bcbuckley13]

@BA_PeteThank you. That fixed the error, but for that cell that says Pending, that one should be Overdue.

[aj1973]

@bcbuckley13 

Replace "Pending" by "Overdue" and vice versa.

[bcbuckley13]

@aj1973  That only swapped them, but didn't fix the actuall issue.