Calculate Time Between Any Stages

[EnrichedUser]

I have an Item Ledger Table that I am wanting to use to determine the time between any two steps and the average time per ItemID and BranchID.

The Table has 5 key columns. 

Branch ID | ITEM ID | Lot Number | Ledger Date | TransactionType

BranchID	ItemID	LotNumber	LedgerDate	TransactionType	Quantity	Index
DC01	500006361	>6K4HBDAA	2/2/21 1:08 PM	ITEM.RECEIVE	1.0000	995
DC01	500008022	>CG1HBDAA	2/3/21 2:01 PM	ORD.PICK	16600.0000	1556
DC01	500008022	>CG1HBDAA	2/4/21 11:12 AM	ORD.SHIP	-16600.0000	1903
DC01	500008022	>CG1HBDAA	2/5/21 7:59 AM	ITEM.PUTAWAY	1318.0000	2243
DC01	500008023	>G5Z9BDAA	2/3/21 11:50 AM	ITEM.PUTAWAY	408.0000	1360
DC01	500008039	>0MMGBDAA	2/5/21 8:34 AM	ORD.PICK	600.0000	2285
DC01	500008039	>0MMGBDAA	2/5/21 10:08 AM	ITEM.PUTAWAY	8468.0000	2391
DC01	500008039	>0MMGBDAA	2/5/21 10:34 AM	ORD.SHIP	-600.0000	2430
DC01	500008042	>9KR3BAAA	2/5/21 8:17 AM	ORD.PICK	2000.0000	2260
DC01	500008042	>9KR3BAAA	2/5/21 10:22 AM	ITEM.PUTAWAY	4524.0000	2422
DC01	500008042	>9KR3BAAA	2/5/21 3:34 PM	ORD.SHIP	-2000.0000	2710
DC01	500008048	>HNBFBDAA	2/4/21 10:55 AM	ORD.SHIP	-1500.0000	1879
DC01	500008106	>5FZBBDAA	2/8/21 8:05 AM	ORD.PICK	100.0000	2867
DC01	500008164	>WGTBBAAA	2/2/21 9:55 AM	ITEM.RECEIVE	27.0000	761
DC01	500008164	>CKDDBAAA	2/2/21 9:56 AM	ITEM.RECEIVE	250.0000	762
DC01	500008164	>CKDDBAAA	2/4/21 5:36 AM	ITEM.MOVE	250.0000	1586
DC01	500008164	>WGTBBAAA	2/4/21 5:38 AM	ITEM.MOVE	27.0000	1587
DC01	500008164	>CKDDBAAA	2/4/21 9:28 AM	ITEM.PUTAWAY	250.0000	1836
DC01	500008164	>WGTBBAAA	2/4/21 9:28 AM	ITEM.PUTAWAY	27.0000	1838
DC01	500008169	>ZWFCBAAA	2/3/21 11:51 AM	ITEM.PUTAWAY	776.0000	1362
DC01	500008169	>8RFGBAAA	2/5/21 8:08 AM	ORD.PICK	3000.0000	2252
DC01	500008169	>8RFGBAAA	2/5/21 10:22 AM	ITEM.PUTAWAY	2294.0000	2420
DC01	500008169	>8RFGBAAA	2/5/21 3:34 PM	ORD.SHIP	-3000.0000	2711
DC01	500008176	>TZRVAAAA	2/5/21 9:26 AM	ORD.PICK	1100.0000	2337
DC01	500008176	>TZRVAAAA	2/5/21 10:24 AM	ITEM.PUTAWAY	1502.0000	2425
DC01	500008176	>TZRVAAAA	2/5/21 3:34 PM	ORD.SHIP	-1100.0000	2712
DC01	500008183	>MTDGBDAA	2/3/21 8:06 AM	ORD.SHIP	-3200.0000	1193
DC01	500008183	>MTDGBDAA	2/3/21 8:42 AM	ORD.PICK	3200.0000	1223
DC01	500008183	>MTDGBDAA	2/4/21 12:33 PM	ORD.SHIP	-3200.0000	2061
DC01	500008183	>MTDGBDAA	2/5/21 10:47 AM	ORD.PICK	3200.0000	2443
DC01	500008196	>RFVGBDAA	2/8/21 7:57 AM	ORD.PICK	250.0000	2863
DC01	500008290	>2W3HBDAA	2/1/21 12:35 PM	ITEM.RECEIVE	500.0000	479
DC01	500008290	>2W3HBDAA	2/3/21 4:53 PM	ITEM.MOVE	500.0000	1584

The Index is the Ledger Date Sorted Oldest to Newest

My goal is to be able to be able to return the time/avg time between any two transaction types for the same ItemID in the same BranchID.

For the ItemID 500076601, I would like to determine the time between every time it moved from "ORD.PICK" to "ORD.SHIP"

(Other Stage Example: ITEM.RECEIVE to ORD.SHIP)

It is possible for an order to have back-to-back transaction types. As an example, a given item may have a transacation of ORD.PICK at 2:02 PM and then a second ORD.PICK at 2:03 before being shipped at 3:43 PM 

Because of this, we will need to consider the first action of first stage and the last action of the second stage when calculating time between stages

• ORD.PICK to ORD.SHIP
  • time between first pick to last ship
• ITEM.RECEIVE to ORD.PICK
  • time between first receive to last pick

[lbendlin]

"When it should be the earliest and latest of the Next Pick and Ship and averging those"

I have absolutely no idea how to implement that. Sorry.

[lbendlin]

I had to add two separate calculated tables for your slicers.

From = VALUES(Ledger[TransactionType])
To = VALUES(Ledger[TransactionType])

The slicers have to be made single select.

The measure then looks like this:

Average Days = 
var f = SELECTEDVALUE('From'[TransactionType])
var t = SELECTEDVALUE('To'[TransactionType])
var tr = ADDCOLUMNS(values(Ledger[ItemID]),"Item",Ledger[ItemID])
var tr1 = ADDCOLUMNS(tr,"minFrom",CALCULATE(min(Ledger[Index]),Ledger[TransactionType]=f,Filter(Ledger,Ledger[ItemID]=[Item]))
                       ,"maxTo"  ,CALCULATE(max(Ledger[Index]),Ledger[TransactionType]=t,Filter(Ledger,Ledger[ItemID]=[Item])))
var tr2 = ADDCOLUMNS(tr1,"Diff",if(not ISBLANK([minFrom]) && not ISBLANK([maxTo]),[maxTo]-[minFrom]))
var tr3 = ADDCOLUMNS(filter(tr2,COALESCE([Diff],0)>0),"Cycle Time"
,CALCULATE(max(Ledger[LedgerDate]),Ledger[TransactionType]=t,Filter(Ledger,Ledger[ItemID]=[Item] && Ledger[Index]=[maxTo]))
-CALCULATE(min(Ledger[LedgerDate]),Ledger[TransactionType]=f,Filter(Ledger,Ledger[ItemID]=[Item] &&Ledger[Index]=[minFrom])))
return AVERAGEX(tr3,[Cycle Time])

The result is in days.

[EnrichedUser]

Thank you for the help!

I believe I got it all set up and was able to do some meaninful testing. 

I have two new tables "From" and "To" that are used in my slicers. 

I have manually checked out 15 parts now. It works perfectly on any items that have only 1 transaction on the 'To' TransactionType. 

This part was shipped 4 seperate times shows that the average days was 75.95 when it should be closer to 1.25 days

From what I could tell, and we can use this part as an example. It is taking the difference between the earliest pick (from) and the latest ship (to). When it should be the earliest and latest of the Next Pick and Ship and averging those. 

10/3/2020 to 12/18/2020 which is ~75.95 days. 

Average Days =

var f = SELECTEDVALUE('From'[TransactionType])

var t = SELECTEDVALUE('To'[TransactionType])

var tr = ADDCOLUMNS(VALUES(ItemLedger[ItemID]), "Item",ItemLedger[ItemID])

var tr1 = ADDCOLUMNS(tr, "minFrom", CALCULATE(MIN(ItemLedger[Index]),ItemLedger[TransactionType] = f, FILTER(ItemLedger,ItemLedger[ItemID] = [Item])),

"maxTo", CALCULATE(MAX(ItemLedger[Index]),ItemLedger[TransactionType] = t, FILTER(ItemLedger, ItemLedger[ItemID] = [Item])))

var tr2 = ADDCOLUMNS(tr1, "Diff", if(not ISBLANK([minFrom]) && not ISBLANK([maxTo]),[maxTo] - [minFrom]))

var tr3 =ADDCOLUMNS(FILTER(tr2,COALESCE([Diff],0)>0), "Cycle Time", CALCULATE(MAX(ItemLedger[LedgerDate]),ItemLedger[TransactionType] = t, FILTER(ItemLedger,ItemLedger[ItemID] = [Item] && ItemLedger [Index] = [maxTo])) - CALCULATE(MIN(ItemLedger[LedgerDate]), ItemLedger[TransactionType] = f, FILTER(ItemLedger, ItemLedger[ItemID] = [Item] && ItemLedger[Index] = [minFrom])))

return

AVERAGEX(tr3, [Cycle Time])

[lbendlin]

Better format - thank you.  But the data is still ambiguous.  Now you have two picks at the same date. Whih one of them is "first" ?  Do you have an index column somewhere that could answer that?

What is a stage?  Would be nice if ITEM.RECEIVE would be part of the sample data.

[EnrichedUser]

I changed the data to include additional stages or "TransactionTypes"

I also added an Index column to show which transaction happened first. (Olderest to Newest on Ledger Date)

Please let me know if that clear it up. Thank you in advance!

[lbendlin]

So what would you do in that case? Ignore the 2:02PM pick?

Please provide sample data in usable format (not as a picture) and show the expected outcome.

[EnrichedUser]

Good question, I think it will best best to consider the time between the first action of the first stage and the last action of the second stage. 

Please let me know if the new table data is usable for testing. I do apprecaite your help.