Hello everyone,
First off thanks to all that take the time to read this, I've tried my best to give as much information as possible to best illustrate the task at hand.
To start: I work in Transportation, so I deal with shipments from both my Vendor and my Consolidator (the carrier bringing me my products). I'm working on a Power Bi report where I can create Transit Lead Times based on when the Carrier picks up the goods, delivers it to their cross dock, takes it from the cross dock to my distribution center and other time metrics.
My issue is this, one of my carriers doubles as a carrier for whats considered Leg 1 & Leg 2 for a Load (the cargo or freight being transported).
Normally, Leg 1 is when the Carrier goes to the vendors warehouse and picks up my goods, this is the Pick Date.
The Carrier then takes the goods to their own warehouse (Drop Date) where they'll consolidate the goods with other goods they picked up for me so that they can build a Full Truck Load to send me multiple shipments (this is still Leg 1).
Leg 2 is the goods leaving the Carrier warehouse (Pick Date) and arriving at my Distribution Center Warehouse (Drop Date).
For every load there is a Pick Date & Drop Date, so it begins to get complex where I want to create a formula that'll calculate the 2nd Leg Pick Date (leaving the Carrier Warehouse) - 1st Leg Drop Date (Arriving at Carrier Warehouse) so I can calculate the processing time for example.
I also want to calculate Leg 2 Drop Date (Arriving at Distribution Center) - Leg 2 Pick Date (Leaving Carrier Warehouse) to calculate the time it takes the freight to move from the Carrier Warehouse to my Distribution Center Warehouse. This is where one Carrier is doubling as a Carrier that picks up the freight in Leg 1 and also delivers the freight to me on Leg 2.
Each Load # is unique and the primary field that can link them together is the D# (Transportation Order #), however Leg 1 may only have one D# because the carriers will pick up a single D# where as Leg 2 will have multiple D#'s because that's the point in the process where multiple goods are consolidated into a single Truckload to maximize the fullest potential of what we can fit on a truck.
So my questions are how do I create a formula that will take dates from Leg 1 - Leg 2 and what do I do when the carrier for Leg 1 & Leg 2 is the same? I've looked at writing IF statements similar to excel but not sure if that's the route I should take in Power Bi
| Carrier Reference | Trailer Number | LOAD # | D# | Create Date | Tender Date | First Pick Arrival Date | Last Drop Arrival Date |
| DELM | . | 188636474 | D680547 | 04/01/2025 08:09:09 | 04/01/2025 08:10:31 | 04/03/2025 15:09:00 | 04/04/2025 07:31:00 |
| DELM | DELM504213 | 189015902 | D679697,D680016,D680035,D680547,D680429,D683079,D683081,D681829,D679683,D679685,D680018,D680927,D681615,D680868,D680023,D680922,D681817,D681614,D681659,D681011,D681121,D680649,D678665,D679691,D680882,D681746,D679688,D680822,D680874,D683787,D679695,D681005,D680915,D680917,D681607,D680873,D681118,D680040,D681825,D680425,D683082,D680646 | 04/09/2025 11:18:00 | 04/09/2025 11:18:01 | 04/10/2025 10:51:00 | 04/15/2025 20:50:00 |
