Global Power BI User Group

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Calculate driving time between 2 data points

  • 1.  Calculate driving time between 2 data points

    Posted Oct 30, 2017 03:02 PM
    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

    ------------------------------
    Alejandra Avila
    Store Intelligence
    México
    5533310967
    ------------------------------


  • 2.  RE: Calculate driving time between 2 data points

    Top Contributor
    Posted Oct 31, 2017 05:20 AM
    Hi.

    Try to use this

    Time = 24,* ( Check out) - (Check in)

    Igor George Abdo
    47 99981 6210 / 47 99178 8884





  • 3.  RE: Calculate driving time between 2 data points

    Silver Contributor
    Posted Oct 31, 2017 06:33 PM
    You are on the right track using index column will give you the desired result. Create index column start from 0 and reference the table and change the index column start from 1. Merge these two tables matching index column, then use custom column where dates are same and store id are different to get the time difference.


    ------------------------------
    Naveen Ajmera
    PowerBIinDubai.com
    Dubai
    ------------------------------



  • 4.  RE: Calculate driving time between 2 data points

    Posted Nov 01, 2017 02:52 PM
    Thanks to all of you! I got a measure that helped me get the desired information, it goes as follows:

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

    This answer was provided to me in the PBI community forum and worked just perfectly!

    ------------------------------
    Alejandra Avila
    Store Intelligence
    México
    5533310967
    ------------------------------