Forum Discussion
Time totals
I have a table which records the number of times someone is late and the time. I need it to be a count of lates and then in the last column the total time for the rows counted. Below is how it is...
No worries.
Can you please supply some of your data (or the PBIX file) - change any personal/private data.
If I can see the data you are working with I can figure out what is going wrong.
Phil
Sure, unfortunately I cannot attach files but here is a table:
| Title | Log off Time | Return Time | Currently_on_break | Length | Lunch | Created | Time stop | Late? | Late_Length |
| Jack | 11:10:28 | 11:31:02 | NO | 00:20:34 | Break | 01/04/2024 11:10 | 00/01/1900 11:30 | ||
| Jack | 14:29:57 | 15:09:58 | NO | 00:40:01 | Lunch | 01/04/2024 14:29 | 00/01/1900 15:09 | ||
| Jack | 16:46:13 | 17:06:33 | NO | 00:20:19 | Break | 01/04/2024 16:46 | 00/01/1900 17:06 | ||
| Jess | 10:50:45 | 11:10:23 | NO | 00:19:38 | Break | 02/04/2024 10:50 | 00/01/1900 11:10 | ||
| Ben | 11:55:41 | 12:34:36 | NO | 00:38:54 | Lunch | 02/04/2024 11:55 | 00/01/1900 12:35 | ||
| Jess | 12:30:53 | 13:12:03 | NO | 00:41:10 | Lunch | 02/04/2024 12:30 | 00/01/1900 13:10 | yes | 00:01:10 |
| Chris | 13:13:22 | 13:29:53 | NO | 00:16:31 | Break | 02/04/2024 13:13 | 00/01/1900 13:33 | ||
| Jess | 14:56:48 | 15:14:26 | NO | 00:17:38 | Break | 02/04/2024 14:56 | 00/01/1900 15:16 | ||
| Chris | 15:12:30 | 15:45:26 | NO | 00:32:56 | Lunch | 02/04/2024 15:12 | 00/01/1900 15:52 | ||
| Chris | 16:52:12 | 17:12:53 | NO | 00:20:39 | Break | 02/04/2024 16:52 | 00/01/1900 17:12 | ||
| Jess | 10:40:19 | 11:01:41 | NO | 00:21:23 | Break | 03/04/2024 10:40 | 00/01/1900 11:00 | yes | 00:01:23 |
| Jack | 11:00:28 | 11:20:30 | NO | 00:20:01 | Break | 03/04/2024 11:00 | 00/01/1900 11:20 | ||
| Ben | 11:51:43 | 12:10:10 | NO | 00:18:27 | Break | 03/04/2024 11:51 | 00/01/1900 12:11 | ||
| Jess | 11:52:24 | 12:31:22 | NO | 00:38:58 | Lunch | 03/04/2024 11:52 | 00/01/1900 12:32 | ||
| Jack | 12:32:54 | 13:12:55 | NO | 00:40:00 | Break | 03/04/2024 12:32 | 00/01/1900 12:52 | yes | 00:20:00 |
| Chris | 13:16:36 | 13:28:02 | NO | 00:11:26 | Break | 03/04/2024 13:16 | 00/01/1900 13:36 | ||
| Ben | 14:20:52 | 14:57:27 | NO | 00:36:35 | Lunch | 03/04/2024 14:20 | 00/01/1900 15:00 | ||
| Jess | 14:32:56 | 14:50:22 | NO | 00:17:26 | Break | 03/04/2024 14:32 | 00/01/1900 14:52 | ||
| Jack | 14:52:22 | 15:12:58 | NO | 00:20:36 | Break | 03/04/2024 14:52 | 00/01/1900 15:12 | ||
| Chris | 15:10:01 | 15:49:36 | NO | 00:39:34 | Lunch | 03/04/2024 15:10 | 00/01/1900 15:50 | ||
| Chris | 16:52:07 | 17:10:19 | NO | 00:18:11 | Break | 03/04/2024 16:52 | 00/01/1900 17:12 | ||
| Ben | 16:52:48 | 17:11:07 | NO | 00:18:18 | Break | 03/04/2024 16:52 | 00/01/1900 17:12 | ||
| Jess | 10:41:01 | 11:00:20 | NO | 00:19:19 | Break | 04/04/2024 10:41 | 00/01/1900 11:01 | ||
| Ben | 11:50:04 | 12:09:53 | NO | 00:19:48 | Break | 04/04/2024 11:50 | 00/01/1900 12:10 | ||
| Jack | 11:57:40 | 12:17:43 | NO | 00:20:03 | Break | 04/04/2024 11:57 | 00/01/1900 12:17 | ||
| Jess | 12:30:18 | 13:08:54 | NO | 00:38:35 | Lunch | 04/04/2024 12:30 | 00/01/1900 13:10 | ||
| Chris | 13:10:24 | 13:31:17 | NO | 00:20:52 | Break | 04/04/2024 13:10 | 00/01/1900 13:30 | ||
| Ben | 14:29:46 | 15:08:11 | NO | 00:38:24 | Lunch | 04/04/2024 14:29 | 00/01/1900 15:09 | ||
| Jess | 14:36:35 | 14:53:36 | NO | 00:17:01 | Break | 04/04/2024 14:36 | 00/01/1900 14:56 | ||
| Chris | 15:20:17 | 15:54:39 | NO | 00:34:22 | Lunch | 04/04/2024 15:20 | 00/01/1900 16:00 | ||
| Ben | 16:57:27 | 17:09:31 | NO | 00:12:03 | Break | 04/04/2024 16:57 | 00/01/1900 17:17 | ||
| Chris | 17:33:43 | 18:21:54 | NO | 00:48:10 | Break | 04/04/2024 17:33 | 00/01/1900 17:53 | yes | 00:28:10 |
| Jess | 10:46:56 | 11:06:25 | NO | 00:19:28 | Break | 05/04/2024 10:46 | 00/01/1900 11:06 | ||
| Ben | 11:53:15 | 12:12:03 | NO | 00:18:48 | Break | 05/04/2024 11:53 | 00/01/1900 12:13 | ||
| Jack | 11:53:29 | 12:16:02 | NO | 00:22:33 | Break | 05/04/2024 11:53 | 00/01/1900 12:13 | yes | 00:02:33 |
| Jess | 12:30:08 | 13:09:32 | NO | 00:39:23 | Lunch | 05/04/2024 12:30 | 00/01/1900 13:10 | ||
| Chris | 13:10:19 | 13:31:50 | NO | 00:21:30 | Break | 05/04/2024 13:10 | 00/01/1900 13:30 | yes | 00:01:30 |
| Jack | 14:32:40 | 15:12:55 | NO | 00:40:14 | Lunch | 05/04/2024 14:32 | 00/01/1900 15:12 | ||
| Ben | 14:45:08 | 15:11:18 | NO | 00:26:09 | Break | 05/04/2024 14:45 | 00/01/1900 15:05 | yes | 00:06:09 |
| Jess | 15:00:33 | 15:17:39 | NO | 00:17:06 | Break | 05/04/2024 15:00 | 00/01/1900 15:20 | ||
| Ben | 15:11:43 | 15:24:09 | NO | 00:12:26 | Break | 05/04/2024 15:11 | 00/01/1900 15:31 | ||
| Chris | 15:13:41 | 15:52:37 | NO | 00:38:55 | Lunch | 05/04/2024 15:13 | 00/01/1900 15:53 | ||
| Ben | 16:53:46 | 17:12:16 | NO | 00:18:30 | Break | 05/04/2024 16:53 | 00/01/1900 17:13 | ||
| Jack | 16:54:10 | 17:14:48 | NO | 00:20:37 | Break | 05/04/2024 16:54 | 00/01/1900 17:14 | ||
| Chris | 17:15:50 | 17:35:29 | NO | 00:19:39 | Break | 05/04/2024 17:15 | 00/01/1900 17:35 | ||
| Jack | 11:28:50 | 11:48:58 | NO | 00:20:07 | Break | 06/04/2024 11:28 | 00/01/1900 11:48 | ||
| Jack | 13:49:39 | 13:49:41 | NO | 00:00:01 | Break | 06/04/2024 13:49 | 00/01/1900 14:09 | ||
| Jack | 13:49:41 | 14:30:11 | NO | 00:40:29 | Lunch | 06/04/2024 13:49 | 00/01/1900 14:29 | ||
| Jack | 16:09:43 | 16:30:24 | NO | 00:20:41 | Break | 06/04/2024 16:09 | 00/01/1900 16:29 | ||
| Chris | 10:50:39 | 11:08:52 | NO | 00:18:13 | Break | 08/04/2024 10:50 | 00/01/1900 11:10 | ||
| Chris | 12:51:26 | 13:30:44 | NO | 00:39:18 | Lunch | 08/04/2024 12:51 | 00/01/1900 13:31 | ||
| Jack | 13:12:37 | 13:18:59 | NO | 00:06:22 | Break | 08/04/2024 13:12 | 00/01/1900 13:32 | ||
| Jack | 13:19:00 | 13:39:09 | NO | 00:20:08 | Break | 08/04/2024 13:19 | 00/01/1900 13:39 | ||
| Ben | 13:30:00 | 13:31:18 | NO | 00:01:17 | Break | 08/04/2024 13:30 | 00/01/1900 13:50 | ||
| Ben | 13:50:04 | 14:09:51 | NO | 00:19:46 | Break | 08/04/2024 13:50 | 00/01/1900 14:10 | ||
| Chris | 15:30:05 | 15:48:17 | NO | 00:18:11 | Break | 08/04/2024 15:30 | 00/01/1900 15:50 | ||
| Jack | 15:38:50 | 16:19:04 | NO | 00:40:13 | Lunch | 08/04/2024 15:38 | 00/01/1900 16:18 | ||
| Ben | 15:54:46 | 16:32:10 | NO | 00:37:23 | Lunch | 08/04/2024 15:54 | 00/01/1900 16:34 | ||
| Jack | 17:12:13 | 17:33:19 | NO | 00:21:05 | Break | 08/04/2024 17:12 | 00/01/1900 17:32 | yes | 00:01:05 |
| Ben | 17:33:56 | 17:52:52 | NO | 00:18:56 | Break | 08/04/2024 17:33 | 00/01/1900 17:53 | ||
| James | 11:30:51 | 11:50:36 | NO | 00:19:44 | Break | 09/04/2024 11:30 | 00/01/1900 11:50 | ||
| Chris | 13:10:46 | 13:27:55 | NO | 00:17:07 | Break | 09/04/2024 13:10 | 00/01/1900 13:30 | ||
| Jess | 13:31:39 | 13:49:34 | NO | 00:17:55 | Break | 09/04/2024 13:31 | 00/01/1900 13:51 | ||
| Ben | 13:34:45 | 13:53:21 | NO | 00:18:35 | Break | 09/04/2024 13:34 | 00/01/1900 13:54 | ||
| James | 13:50:59 | 14:40:52 | NO | 00:49:52 | Lunch | 09/04/2024 13:51 | 00/01/1900 14:30 | yes | 00:09:52 |
Thanks Lakka00
Here's a PBIX file with the solution
I had to modify the DAX measure for the Late Count as your column names were different to what I originally had
Count Lates = CALCULATE(COUNTROWS('DataTable'), FILTER(ALL('DataTable'), 'DataTable'[Late?] ="yes" && 'DataTable'[Title] = SELECTEDVALUE('DataTable'[Title])))Regards
Phil
Hi, thank you. However I cannot open the file due to incompatible version types, I will try get mine updated but I am assuming I will not be able to due to IT restrictions.
Hi Lakka00 ,
I think your solution is great, PhilipTreacy . It worked like a charm! I think you can update your Power BI Desktop version and try to open the .pbix file provided by PhilipTreacy below.
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.