Forum Discussion
Getting Date from Next Row
Need DAX code to get the date from the row of that vehicle ID's next pickup. Have Vehicle ID data that contains a Pickup Date and a Drop Date with the State it drops in. I need to know how long each vehicle sat in each state (each row) by getting the next pickup date for that vehicle and taking the date difference from the Drop Date and the Next Pick Up Date. I did add an Index column in Power Query and did this as a Calculated Column but the dataset is very large and takes way too long to refresh. Here is an example of the data:
| VehicleID | PickupDate | DropDate | DropState | NextPickupDate | DaysSitting |
| 3444 | 10/1/2024 | 10/3/2024 | MO | 10/6/2024 | 3 |
| 4100 | 10/1/2024 | 10/2/2024 | IL | 10/5/2024 | 3 |
| 4100 | 10/5/2024 | 10/6/2024 | KY | 10/7/2024 | 1 |
| 3444 | 10/6/2024 | 10/7/2024 | OH | 10/9/2024 | 2 |
| 4100 | 10/7/2024 | 10/8/2024 | TN | 10/9/2024 | 1 |
Thanks
5 Replies
Here's the code for my Next Pickup Date measure:
Next Pick Up = CALCULATE(MIN('Table'[PickupDate]), OFFSET(1, ALL('Table'), ORDERBY('Table'[PickupDate], ASC), PARTITIONBY('Table'[VehicleID]), MATCHBY('Table'[VehicleID], 'Table'[PickupDate])))Note - if you've got an index column, you can use that in the MATCHBY instead of VehicleID and PickupDate.
You can then do a simple subtraction to get the days sitting.
Thank you, as an added wrinkle to this that I forgot, if the vehicle has a drop date before today's date and no pickup after that, is there a way to always default the Next Pickup Date calculation to today's date? Then the Next Pickup Date would continue to automatically update and thus update the amount of time the driver is sitting, until the driver receives their next pickup and another entry is added to the database.
Hi, jeffw14
You can try the following methods.
Measure = Var _Nextpickup=CALCULATE(MIN('Table'[PickupDate]),FILTER(ALLEXCEPT('Table','Table'[VehicleID]),[PickupDate]>MAX('Table'[PickupDate]))) Var _diff=ABS(DATEDIFF(MAX('Table'[DropDate]),_Nextpickup,DAY)) RETURN IF(_Nextpickup<>BLANK(),_diff)Please see the attached document.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you, as an added wrinkle to this that I forgot, if the vehicle has a drop date before today's date and no pickup after that, is there a way to always default the Next Pickup Date calculation to today's date? Then the Next Pickup Date would continue to automatically update and thus update the amount of time the driver is sitting, until the driver receives their next pickup and another entry is added to the database.
