Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 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 |
What I need to calculate is the "driving time" from Store A to Store B (in this case it would be the time difference between 9:58 am to 10:56 am), in excel it seems pretty straight forward but as my data base has information for several ID Users, ID Stores and dates it becomes complicated, also I need to consider only driving times from the same day only, in the above example Stores A to D were visited on the same day and Stores W to Z correspond to the next working day. I believe maybe by adding sort of an "index" column which can identify the first and last entries for each day could be a star point but I'm still not sure if the final calculation should be done with a measure or a calculated column, I'll appreciate any thoughts on how to tackle this!
Regards
Solved! Go to Solution.
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 () )
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 () )
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 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
Gwin
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
109 | |
108 | |
108 | |
91 | |
61 |
User | Count |
---|---|
171 | |
139 | |
133 | |
102 | |
86 |