Calculate driving time between 2 data points

[aavilap]

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

[erik_tarnvik · Accepted solution]

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 () )

[Gwin]

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

[aavilap]

@erik_tarnvik Thanks! It worked just perfectly!