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