Getting Earlier Function To Work

[comacabana]

Hi all,

I'm back with another question for you guys. I'm stuck on labelling customer orders on my table. 

The gist of it is, I want to label each order as either Incomplete Delivery (Late), Incomplete Delivery (Future Order), Fully Delivered Before Wanted Date or Fully Delivered After Wanted Date based on criterea. 

I'm pretty certain I have to use the earlier function but I cannot for the life of me to figure it out. The critera I need for the above is

Incomplete Delivery (Late) =
1) Order contains picked, partially delivered, released or reserved status
2) Wanted date is less than todays date

Incomplete Delivery (Future Order) =
1) Order contains picked, partially delivered, released or reserved status
2) Wanted date is greater than todays date

Full Delivery Before Wanted Date =
1) Order only contains invoiced/closed and/or delivered status
2) Wanted date is less than actual shipped date

Full Delivery After Wanted Date =
1) Order only contains invoiced/closed and/or delivered status
2) Wanted date is greater than actual shipped date

Problem is one customer order can contain multiple parts and not everything is shipped on the same date; hence why I'm thinking I need the earlier function. 

Sample Data is as follows. What I'd like for order number C280820 to display is Full Delivery Before Wanted Date whereas irder C280898 should display Incomplete Delivery (Late).

Appreciate you guys' help!

Created	Order No	Shipp To Country	Status	Part	Wanted Delivery Date/Time	Actual ship date
1/07/2022	C280820	US	Invoiced/Closed	Bearing	2/08/2022	25/07/2022
1/07/2022	C280820	US	Invoiced/Closed	Springs	2/08/2022	25/07/2022
2/07/2022	C280898	PE	Delivered	Valve	19/08/2022	9/08/2022
2/07/2022	C280898	PE	Delivered	Touchscreen	19/08/2022	9/08/2022
2/07/2022	C280898	PE	Partially Delivered	Springs	19/08/2022
2/07/2022	C280898	PE	Reserved	Valve	19/08/2022
2/07/2022	C280898	PE	Released	Valve	19/08/2022
25/07/2022	C280898	PE	Reserved	Touchscreen	5/08/2022
4/08/2022	C280898	PE	Delivered	Deflector	5/08/2022	9/08/2022
4/08/2022	C280898	PE	Delivered	Bearing	5/08/2022	9/08/2022

[comacabana]

Hi @ryan_mayu thank you for the code! 
I'm not sure if I explained the logic correctly as you mentioned, however in your code I noticed that the status "Invoiced/Closed" is considered as an incomplete delivery. However It's considered as delivered. I'm assuming something needs to be edited? 

[ryan_mayu]

yes, you need to modify the coding since the logic you provided is not very clear

Incomplete Delivery (Late) =
1) Order contains picked, partially delivered, released or reserved status (OR relationship between 4 status?)
2) Wanted date is less than todays date

(AND between 1 and 2?)

Incomplete Delivery (Future Order) =
1) Order contains picked, partially delivered, released or reserved status
2) Wanted date is greater than todays date

Full Delivery Before Wanted Date =
1) Order only contains invoiced/closed and/or delivered status

(why we have and / or , it's impossble to write the coding)
2) Wanted date is less than actual shipped date

Full Delivery After Wanted Date =
1) Order only contains invoiced/closed and/or delivered status
2) Wanted date is greater than actual shipped date

[comacabana]

Fair enough thank you. I can definitely see why my logic wasn't clear

[ryan_mayu]

you are welcome

[ryan_mayu · Accepted solution]

@comacabana 

you logic description is not clear, especially the AND  OR conditions

maybe you can try this and edit based on your actual logic

Column = 
VAR _picked=maxx(FILTER('Table','Table'[Order No]=EARLIER('Table'[Order No])&&'Table'[Status]="Picked"),'Table'[Order No])
VAR _pd=maxx(FILTER('Table','Table'[Order No]=EARLIER('Table'[Order No])&&'Table'[Status]="Partially Delivered"),'Table'[Order No])
VAR _released=maxx(FILTER('Table','Table'[Order No]=EARLIER('Table'[Order No])&&'Table'[Status]="Released"),'Table'[Order No])
VAR _reserved=maxx(FILTER('Table','Table'[Order No]=EARLIER('Table'[Order No])&&'Table'[Status]="Reserved"),'Table'[Order No])
VAR _IC=maxx(FILTER('Table','Table'[Order No]=EARLIER('Table'[Order No])&&'Table'[Status]="Invoiced/Closed"),'Table'[Order No])
VAR _delivered=maxx(FILTER('Table','Table'[Order No]=EARLIER('Table'[Order No])&&'Table'[Status]="Delivered"),'Table'[Order No])
return if((_picked<>""||_pd<>""||_released<>""||_reserved<>"")=true(),if('Table'[Wanted Delivery Date/Time]<today(),"ID(Late)","ID(future order)"),if((_IC<>""||_delivered<>"")=true(),if('Table'[Wanted Delivery Date/Time]<'Table'[Actual ship date],"Before","After")))

pls see the attachment below