Creating Ranking & Turn Around Time for complex Data

[aboliverABC]

I am trying to calculate Turn-Around-Time from the time a customer registers, to the 1st time the order is completed & shipped. However, there are a few factors that need to be considered: 

1. The Customer may have more than 1 Registration.
2. A customer's Registration may have more than 1 order (unfortunately, there is no key to connect the orders to the registrations).
3. The data set I am using is for a specific time frame.  So some customers may have orders from previous registrations in the data set, but I do not care about those orders. 
4. I only need the Turn-Around-Time for the 1st Completed Order following a Completed Registration. There is an ‘Order Type’ in my data set that distinguish between Initial Fills & Refills, however it is not consistently marked.

I originally had a Registration Table & an Order Table.  I organized a Combined_Table (see below) based on Customer ID & "Trigger Date".  There is a step in the Registration process that "triggers" an Order to be auto generated.  Resulting in the Order Create Date often times being before the Registration date by a few minutes.  In other words, the Trigger Date is when the Order is Created, and may or may not correspond to the Registration's Auto Generated task.  

My thought process is to create a sequence column that will help me identify the correct Registration Date to use in my Turn-Around-Time calculation.  However, I can’t even get that right. I currently am using a calculated column to Identify the Sequence defined as:

Sequence =

    RANKX(

        filter(Combined_Table,

            (Combined_Table[Customer_Id]=earlier(Combined_Table[Customer_Id])&& Combined_Table [Order_num] <> blank())),

        Combined_Table [Order_Created_Date],

        ,

        asc,

        Dense)

Can someone help me correctly update my Sequence formula, as shown in the example below?

Can someone help me correctly calculate the TAT, as shown in the examples below?

Customer ID	Registration #	Registration Date	Registration Status	Trigger_Date	Order #	Order Created Date	Order Status	Order Type	Sequence	Sequence Updated	Order Ship Date	TAT
22528192				09/06/2022 17:36	29463032	09/06/2022 17:36	Completed	Refill	1		09/06/2022 17:42
22528192	30434426	10/14/2022 21:28	Completed	10/14/2022 21:26					1
22528192				10/18/2022 17:05	30540462	10/18/2022 17:05	Cancelled	Initial Fill	2	1
22528192				10/18/2022 17:10	30540743	10/18/2022 17:10	Completed	Initial Fill	3	2	10/18/2022 17:18	= 10/18/22 17:18 - 10/14/22 21:28
22528192	30541214	10/19/2022 15:29	Completed	10/19/2022 15:25	30565974	10/19/2022 15:25	Cancelled	Initial Fill	4	1
22528192				10/20/2022 15:21	30594041	10/20/2022 15:21	Completed	Refill	5	2	10/20/2022 15:22	= 10/20/22 15:22 - 10/19/22 15:29
22528192				10/28/2022 16:32	30763119	10/28/2022 16:32	Cancelled	Reshipment	6	3	10/28/2022 16:37
23179529	29497233	09/07/2022 18:36	Completed	09/07/2022 18:34	29497416	09/07/2022 18:34	Cancelled	Initial Fill	1	1
23179529				09/14/2022 22:14	29670045	09/14/2022 22:14	Cancelled	Initial Fill	2	2	09/14/2022 22:15
23179529	29673860	09/15/2022 12:08	Completed	09/15/2022 12:07	29673885	09/15/2022 12:07	Completed	Initial Fill	3	1	09/15/2022 12:10	= 09/15/22 12:10 -09/15/22 12:08
23179529				10/10/2022 19:24	30262790	10/10/2022 19:24	Completed	Refill	4	2	10/10/2022 19:24
25092056				08/23/2022 20:02	29187290	08/23/2022 20:02	Completed	Refill	1		08/23/2022 20:04
25092056				09/21/2022 20:43	29822703	09/21/2022 20:43	Completed	Refill	2		09/21/2022 20:43
25092056				10/24/2022 16:24	30651645	10/24/2022 16:24	Completed	Initial Fill	3		10/24/2022 16:26
25092056	30799070	10/31/2022 21:28	Completed	10/31/2022 20:54	30799199	10/31/2022 20:54	Cancelled	Initial Fill	4	1
25092056	30801013	10/31/2022 22:02	Completed	10/31/2022 21:55	30801099	10/31/2022 21:55	Cancelled	Initial Fill	5	1
25092056				11/21/2022 16:40	32620903	11/21/2022 16:40	Completed	Refill	6	2	11/21/2022 16:44	= 11/21/22 16:44 - 10/31/22 22:02
35224529				08/23/2022 18:15	29180785	08/23/2022 18:15	Completed	Initial Fill	1		08/23/2022 18:23
35224529	29140535	08/23/2022 18:18	Completed	08/23/2022 18:16	29180838	08/23/2022 18:16	Cancelled	Initial Fill	2	1
35224529	29692973	09/15/2022 19:30	Completed	09/15/2022 19:29	29693087	09/15/2022 19:29	Cancelled	Refill	3	1	09/15/2022 19:40
35224529				09/19/2022 19:35	29756600	09/19/2022 19:35	Completed	Initial Fill	4	2	09/19/2022 19:41	= 9/19/22 19:41 - 9/15/22 19:30
35264038	29250768	08/25/2022 19:11	Completed	08/25/2022 19:10	29252036	08/25/2022 19:10	Cancelled	Initial Fill	1	1
35264038	29325195	08/30/2022 13:59	Completed	08/30/2022 13:58	29325270	08/30/2022 13:58	Cancelled	Initial Fill	2	1
35264038				08/30/2022 14:25	29326507	08/30/2022 14:25	Completed	Initial Fill	3	2	08/30/2022 14:27	= 8/30/22 14:27 - 8/30/22 13:59
35264038				09/28/2022 16:14	29963187	09/28/2022 16:14	Completed	Refill	4	3	09/28/2022 16:15
35264038				10/26/2022 18:02	30715089	10/26/2022 18:02	Completed	Refill	5	4	10/27/2022 16:15
35264038				11/21/2022 18:12	32626342	11/21/2022 18:12	Completed	Refill	6	5	11/21/2022 18:12

[Anonymous · Accepted solution]

Hi @aboliverABC 

You cam create two column:

Sequence = var a=FILTER('Table','Table'[Customer ID]=EARLIER('Table'[Customer ID])&&'Table'[Registration #]<>BLANK())
return SWITCH(TRUE(),[Trigger_Date]>=MINX(a,[Trigger_Date])&&[Trigger_Date]<MAXX(a,[Trigger_Date])&&[Order #]<>BLANK(),RANKX(FILTER('Table','Table'[Customer ID]=EARLIER('Table'[Customer ID])&&[Trigger_Date]>=MINX(a,[Trigger_Date])&&[Trigger_Date]<MAXX(a,[Trigger_Date])&&[Order #]<>BLANK()),[Trigger_Date],,ASC,Dense),'Table'[Trigger_Date]>=MAXX(a,[Trigger_Date]),RANKX(FILTER('Table','Table'[Customer ID]=EARLIER('Table'[Customer ID])&&'Table'[Trigger_Date]>=MAXX(a,[Trigger_Date])),[Trigger_Date],,ASC,Dense),BLANK())

TAT_MINUTE = var a=FILTER('Table','Table'[Customer ID]=EARLIER('Table'[Customer ID])&&'Table'[Registration #]<>BLANK())
var b=FILTER('Table','Table'[Customer ID]=EARLIER('Table'[Customer ID])&&[Trigger_Date]>=MINX(a,[Trigger_Date])&&[Trigger_Date]<MAXX(a,[Trigger_Date])&&[Order Status]="Completed")
var c=FILTER('Table','Table'[Customer ID]=EARLIER('Table'[Customer ID])&&[Trigger_Date]>=MAXX(a,[Trigger_Date])&&[Order Status]="Completed")
return SWITCH(TRUE(),[Trigger_Date]>=MINX(a,[Trigger_Date])&&[Trigger_Date]<MAXX(a,[Trigger_Date])&&[Order Ship Date]=MINX(b,[Order Ship Date]),DATEDIFF(MINX(A,[Registration Date]),[Order Ship Date],MINUTE),'Table'[Trigger_Date]>=MAXX(a,[Trigger_Date])&&[Order Ship Date]=MINX(c,[Order Ship Date]),DATEDIFF(MAXX(A,[Registration Date]),[Order Ship Date],MINUTE),BLANK())

Best Regards!

Yolo Zhu

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

[amitchandak]

@aboliverABC ,Based on what I got

Assuming sequence is working fine, or do small change

Sequence =
RANKX(
filter(Combined_Table,
Combined_Table[Customer_Id]=earlier(Combined_Table[Customer_Id])&& not(isblank(Combined_Table [Order_num]))),
Combined_Table [Order_Created_Date], , asc, Dense)

then create a diff

time diff = datediff( coalease( maxx(filter( Combined_Table, Combined_Table[Customer_Id]=earlier(Combined_Table[Customer_Id]) && [Sequence] = earlier([Sequence]) -1 ), [Order_Created_Date]), [Order_Created_Date]) ,[Order_Created_Date], second)