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!

  • Gwin's avatar
    Gwin
    Regular Visitor
    8 years ago

    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