Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
aavilap
Helper I
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 UserID StoreCheck InCheck OutTime Diff
1A10/18/2017 9:1510/18/2017 9:580:43:12
1B10/18/2017 10:5610/18/2017 11:320:36:00
1C10/18/2017 12:2910/18/2017 13:200:50:24
1D10/18/2017 14:1710/18/2017 15:221:04:48
1W10/19/2017 9:0510/19/2017 9:260:21:36
1X10/19/2017 10:2410/19/2017 11:501:26:24
1Y10/19/2017 12:4810/19/2017 13:450:57:36
1Z10/19/2017 14:4310/19/2017 15:290: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
erik_tarnvik
Solution Specialist
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 () )

View solution in original post

3 REPLIES 3
erik_tarnvik
Solution Specialist
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 () )

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 UserID StoreCheck InCheck OutTime Diff
1A10/18/2017 9:1510/18/2017 9:580:43:12
1B10/18/2017 10:5610/18/2017 11:320:36:00
1C10/18/2017 12:2910/18/2017 13:200:50:24
1D10/18/2017 14:1710/18/2017 15:221:04:48
1W10/19/2017 9:0510/19/2017 9:260:21:36
1X10/19/2017 10:2410/19/2017 11:501:26:24
1Y10/19/2017 12:4810/19/2017 13:450:57:36
1Z10/19/2017 14:4310/19/2017 15:290:46:05

 

Thank you in advance

Gwin

@erik_tarnvik Thanks! It worked just perfectly!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.