Forum Discussion
Seeking help implementing Dax - DriveTime
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
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
3 Replies
The error message suggests you have your check in and check out date comparisons back to front.
Do you mind running my DAX? the dates are labelled properly. Thanks
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
