Seeking help implementing Dax - DriveTime

[Gwin]

Can anyone help please?

Using this function from erik_tarnvik :

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 an error massage:

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

Thank you in advance.

Regards

Gwin

[v-yulgu-msft · Accepted solution]

Hi @Gwin,

As the error message prompted, in DATEDIFF function, the start date cannot be greater than the end date. 

Syntax: DATEDIFF(<start_date>, <end_date>, <interval>)

So, please make a little modification to your formula:

DriveTime 2 = 
VAR DT =
    DATEDIFF (
        MAX ( Driving[Check In] ),
        MAXX (
            FILTER (
                ALLSELECTED ( Driving ),
                Driving[ID User] = MAX ( Driving[ID User] )
                    && Driving[Check Out] < MAX ( Driving[Check In] )
            ),
            Driving[Check Out]
        ),
        
        DAY
    )
VAR DM =
    DATEDIFF (
        MAX ( Driving[Check In] ),
        MAXX (
            FILTER (
                ALLSELECTED ( Driving ),
                Driving[ID User] = MAX ( Driving[ID User] )
                    && Driving[Check Out] < MAX ( Driving[Check In] )
            ),
            Driving[Check Out]
        ),
        
        MINUTE
    )
RETURN
    IF ( DT = 0, DM, BLANK () )

Best regards,
Yuliana Gu

[MattAllington]

The error message suggests you have your check in and check out date comparisons back to front. 

[Gwin]

Do you mind running my DAX? the dates are labelled properly. Thanks