Yes/No Column in Powerbi

[bohon79]

Good Morning, this has probally already been ask but cant seem to find it if it has

I have 3 columns
Completion date
Due Date
On Time

Trying to get where it annotates yes or no in the On time column.

1. Annotate Yes if completion date is on or before the Due Date
2. Annotate No if completion date is after the Due Date
3. This is where i am having trouble, Annotate Yes if blank and todays date is after the due date or No if todays date is on or before the due date

I am able to do the conditional column piece but getting stuck on number 3 on how to incorporate it into 1 and 2.

any help would be greatly appreciated.

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangeType = Table.TransformColumnTypes(Source,{{"Completion Date", type date}, {"Due Date", type date}}),
    Result = Table.AddColumn(ChangeType, "On Time?", each try if [Completion Date] <= [Due Date] then "Yes" else "No" otherwise if Date.From(DateTime.LocalNow()) <= [Due Date] then "Yes" else "No")
in
    Result

Book2
	A	B	C	D	E	F	G	H	I
1	Task	Completion Date	Due Date		Task	Completion Date	Due Date	On Time?
2	A	4/5/2023	3/20/2023		A	4/5/2023	3/20/2023	No
3	B	2/22/2023	3/7/2023		B	2/22/2023	3/7/2023	Yes
4	C	1/26/2023	3/11/2023		C	1/26/2023	3/11/2023	Yes
5	D		2/22/2023		D		2/22/2023	No
6	E	3/2/2023	3/17/2023		E	3/2/2023	3/17/2023	Yes
7	F	2/10/2023	3/23/2023		F	2/10/2023	3/23/2023	Yes
8	G		4/28/2023		G		4/28/2023	Yes
9	H	1/5/2023	3/11/2023		H	1/5/2023	3/11/2023	Yes
10	I	2/22/2023	3/23/2023		I	2/22/2023	3/23/2023	Yes
11	J		5/14/2023		J		5/14/2023	Yes
12	K	4/8/2023	3/31/2023		K	4/8/2023	3/31/2023	No
13	L		3/19/2023		L		3/19/2023	No
14	M	3/17/2023	2/16/2023		M	3/17/2023	2/16/2023	No
15	N	2/16/2023	3/9/2023		N	2/16/2023	3/9/2023	Yes
16	O	4/11/2023	2/23/2023		O	4/11/2023	2/23/2023	No
17	P	3/9/2023	3/11/2023		P	3/9/2023	3/11/2023	Yes
18
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2:E17	Expression	=H2="No"	text	NO

 

[bohon79]

I was seeing if this can be done in PowerBI and not excel and then import to PowerBI. This looks like it is for Excel and not PowerBI.