Forum Discussion

aavilap's avatar
aavilap
Helper I
8 years ago
Solved

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 St...
Need Help
  • erik_tarnvik's avatar
    erik_tarnvik
    8 years ago

    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's avatar
erik_tarnvik
Solution Specialist
8 years ago

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 () )
aavilap's avatar
aavilap
Helper I
8 years ago

erik_tarnvik Thanks! It worked just perfectly!