Help with converting excel formula to Power BI

[siddrow]

Hi

I'm after some help to convert the below excel formula so I can create a custom column in my Power BI query

 =IFS(
    AND(ISNUMBER([@[Date Completed]]),ISBLANK([@Status])), "Missing status",
    [@Status] <> "completed", "",
    ISBLANK([@[Date Completed]]), "Missing completed date",
    AND(ISNUMBER([@[Date Completed]]),[@[Date Completed]]<=[@[Due Date]]), "Completed on time",
    TRUE, "Completed late"
)

[v-xiaotang · Accepted solution]

Hi @siddrow 

Thanks for reaching out to us.

You can try this measure

Measure = 
SWITCH(TRUE(),
MIN('Table'[Status])=BLANK(),"Missing status",
MIN('Table'[Status])<>"completed","",
MIN('Table'[Date Completed])=BLANK(),"Missing completed date",
MIN('Table'[Date Completed])<=MIN('Table'[Due Date]),"Completed on time",
"Completed late"
)

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@siddrow , can you share sample data and expected results

[siddrow]

Hi

Please see below. 

Due DateDate CompletedStatusComment

1/01/2022	1/01/2022	Completed	Completed on time
1/01/2022	31/12/2021	Completed	Completed on time
1/01/2022		Completed	Missing completed date
1/01/2022	2/02/2022	Completed	Completed late
1/01/2022	3/03/2022		Missing status
1/01/2022	31/12/2021	Blabla
1/01/2022