Getting Date from Next Row

[jeffw14]

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

[v-zhangtin-msft]

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.

[jeffw14]

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.

[v-zhangtin-msft]

Hi, @jeffw14 

Please provide some of the sample data and the output you expect.

Best Regards

[vicky_]

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.

[jeffw14]

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.