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