cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

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

1 ACCEPTED SOLUTION
Solution Specialist

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 3
Solution Specialist

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 () )```
Regular Visitor

Dear Erik

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

Gwin

Helper I

@erik_tarnvik Thanks! It worked just perfectly!