Forum Discussion

EnrichedUser's avatar
EnrichedUser
Icon for Helper III rankHelper III
5 years ago

Calculate Time Between Any Stages

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

 

BranchIDItemIDLotNumberLedgerDateTransactionTypeQuantityIndex
DC01500006361>6K4HBDAA2/2/21 1:08 PMITEM.RECEIVE1.0000995
DC01500008022>CG1HBDAA2/3/21 2:01 PMORD.PICK16600.00001556
DC01500008022>CG1HBDAA2/4/21 11:12 AMORD.SHIP-16600.00001903
DC01500008022>CG1HBDAA2/5/21 7:59 AMITEM.PUTAWAY1318.00002243
DC01500008023>G5Z9BDAA2/3/21 11:50 AMITEM.PUTAWAY408.00001360
DC01500008039>0MMGBDAA2/5/21 8:34 AMORD.PICK600.00002285
DC01500008039>0MMGBDAA2/5/21 10:08 AMITEM.PUTAWAY8468.00002391
DC01500008039>0MMGBDAA2/5/21 10:34 AMORD.SHIP-600.00002430
DC01500008042>9KR3BAAA2/5/21 8:17 AMORD.PICK2000.00002260
DC01500008042>9KR3BAAA2/5/21 10:22 AMITEM.PUTAWAY4524.00002422
DC01500008042>9KR3BAAA2/5/21 3:34 PMORD.SHIP-2000.00002710
DC01500008048>HNBFBDAA2/4/21 10:55 AMORD.SHIP-1500.00001879
DC01500008106>5FZBBDAA2/8/21 8:05 AMORD.PICK100.00002867
DC01500008164>WGTBBAAA2/2/21 9:55 AMITEM.RECEIVE27.0000761
DC01500008164>CKDDBAAA2/2/21 9:56 AMITEM.RECEIVE250.0000762
DC01500008164>CKDDBAAA2/4/21 5:36 AMITEM.MOVE250.00001586
DC01500008164>WGTBBAAA2/4/21 5:38 AMITEM.MOVE27.00001587
DC01500008164>CKDDBAAA2/4/21 9:28 AMITEM.PUTAWAY250.00001836
DC01500008164>WGTBBAAA2/4/21 9:28 AMITEM.PUTAWAY27.00001838
DC01500008169>ZWFCBAAA2/3/21 11:51 AMITEM.PUTAWAY776.00001362
DC01500008169>8RFGBAAA2/5/21 8:08 AMORD.PICK3000.00002252
DC01500008169>8RFGBAAA2/5/21 10:22 AMITEM.PUTAWAY2294.00002420
DC01500008169>8RFGBAAA2/5/21 3:34 PMORD.SHIP-3000.00002711
DC01500008176>TZRVAAAA2/5/21 9:26 AMORD.PICK1100.00002337
DC01500008176>TZRVAAAA2/5/21 10:24 AMITEM.PUTAWAY1502.00002425
DC01500008176>TZRVAAAA2/5/21 3:34 PMORD.SHIP-1100.00002712
DC01500008183>MTDGBDAA2/3/21 8:06 AMORD.SHIP-3200.00001193
DC01500008183>MTDGBDAA2/3/21 8:42 AMORD.PICK3200.00001223
DC01500008183>MTDGBDAA2/4/21 12:33 PMORD.SHIP-3200.00002061
DC01500008183>MTDGBDAA2/5/21 10:47 AMORD.PICK3200.00002443
DC01500008196>RFVGBDAA2/8/21 7:57 AMORD.PICK250.00002863
DC01500008290>2W3HBDAA2/1/21 12:35 PMITEM.RECEIVE500.0000479
DC01500008290>2W3HBDAA2/3/21 4:53 PMITEM.MOVE500.00001584

 

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
Need Help

7 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      EnrichedUser
      Icon for Helper III rankHelper III
      5 years ago

      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. 

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      EnrichedUser
      Icon for Helper III rankHelper III
      5 years ago

      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's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      EnrichedUser
      Icon for Helper III rankHelper III
      5 years ago

      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's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    "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.