Forum Discussion
Calculate driving time between 2 data points
Hello! I've been having a hard time trying to figure out how to calculate the time between two data points within my database, hope you guys can help! My data looks as follows:
| ID User | ID Store | Check In | Check Out | Time Diff |
| 1 | A | 10/18/2017 9:15 | 10/18/2017 9:58 | 0:43:12 |
| 1 | B | 10/18/2017 10:56 | 10/18/2017 11:32 | 0:36:00 |
| 1 | C | 10/18/2017 12:29 | 10/18/2017 13:20 | 0:50:24 |
| 1 | D | 10/18/2017 14:17 | 10/18/2017 15:22 | 1:04:48 |
| 1 | W | 10/19/2017 9:05 | 10/19/2017 9:26 | 0:21:36 |
| 1 | X | 10/19/2017 10:24 | 10/19/2017 11:50 | 1:26:24 |
| 1 | Y | 10/19/2017 12:48 | 10/19/2017 13:45 | 0:57:36 |
| 1 | Z | 10/19/2017 14:43 | 10/19/2017 15:29 | 0:46:05 |
What I need to calculate is the "driving time" from Store A to Store B (in this case it would be the time difference between 9:58 am to 10:56 am), in excel it seems pretty straight forward but as my data base has information for several ID Users, ID Stores and dates it becomes complicated, also I need to consider only driving times from the same day only, in the above example Stores A to D were visited on the same day and Stores W to Z correspond to the next working day. I believe maybe by adding sort of an "index" column which can identify the first and last entries for each day could be a star point but I'm still not sure if the final calculation should be done with a measure or a calculated column, I'll appreciate any thoughts on how to tackle this!
Regards
So here is a measure that will return the driving time to a spot from the previous spot in minutes. It will return a BLANK() if the previous spot was checked out from on a previous day.
DriveTime = VAR DT = DATEDIFF ( MAXX ( FILTER ( ALLSELECTED ( Driving ), Driving[ID User] = MAX ( Driving[ID User] ) && Driving[Check Out] < MAX ( Driving[Check In] ) ), Driving[Check Out] ), MAX ( Driving[Check In] ), DAY ) VAR DM = DATEDIFF ( MAXX ( FILTER ( ALLSELECTED ( Driving ), Driving[ID User] = MAX ( Driving[ID User] ) && Driving[Check Out] < MAX ( Driving[Check In] ) ), Driving[Check Out] ), MAX ( Driving[Check In] ), MINUTE ) RETURN IF ( DT = 0, DM, BLANK () )
3 Replies
So here is a measure that will return the driving time to a spot from the previous spot in minutes. It will return a BLANK() if the previous spot was checked out from on a previous day.
DriveTime = VAR DT = DATEDIFF ( MAXX ( FILTER ( ALLSELECTED ( Driving ), Driving[ID User] = MAX ( Driving[ID User] ) && Driving[Check Out] < MAX ( Driving[Check In] ) ), Driving[Check Out] ), MAX ( Driving[Check In] ), DAY ) VAR DM = DATEDIFF ( MAXX ( FILTER ( ALLSELECTED ( Driving ), Driving[ID User] = MAX ( Driving[ID User] ) && Driving[Check Out] < MAX ( Driving[Check In] ) ), Driving[Check Out] ), MAX ( Driving[Check In] ), MINUTE ) RETURN IF ( DT = 0, DM, BLANK () )erik_tarnvik Thanks! It worked just perfectly!
Dear Erik
Can you please help?
I have generated DriveTime from the function below:
DriveTime = VAR DT = DATEDIFF ( MAXX ( FILTER ( ALLSELECTED ( Driving ), Driving[ID User] = MAX ( Driving[ID User] ) && Driving[Check Out] < MAX ( Driving[Check In] ) ), Driving[Check Out] ), MAX ( Driving[Check In] ), DAY ) VAR DM = DATEDIFF ( MAXX ( FILTER ( ALLSELECTED ( Driving ), Driving[ID User] = MAX ( Driving[ID User] ) && Driving[Check Out] < MAX ( Driving[Check In] ) ), Driving[Check Out] ), MAX ( Driving[Check In] ), MINUTE ) RETURN IF ( DT = 0, DM, BLANK () )I am getting this Error Message:
MdxScript(Model) (5, 5) Calculation error in measure 'Driving'[DriveTime]: In DATEDIFF function, the start date cannot be greater than the end date
My data is
ID User ID Store Check In Check Out Time Diff 1 A 10/18/2017 9:15 10/18/2017 9:58 0:43:12 1 B 10/18/2017 10:56 10/18/2017 11:32 0:36:00 1 C 10/18/2017 12:29 10/18/2017 13:20 0:50:24 1 D 10/18/2017 14:17 10/18/2017 15:22 1:04:48 1 W 10/19/2017 9:05 10/19/2017 9:26 0:21:36 1 X 10/19/2017 10:24 10/19/2017 11:50 1:26:24 1 Y 10/19/2017 12:48 10/19/2017 13:45 0:57:36 1 Z 10/19/2017 14:43 10/19/2017 15:29 0:46:05 Thank you in advance
Gwin
