Time totals

[Lakka00]

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 appearing, each colour represents the same person, there should ideally be one entry for each person.

[PhilipTreacy]

Hi @Lakka00 

Download PBIX file with the example below

This is done more easily in Power Query than in DAX.  The DAX solution is very fiddly.

Reason being is your Late Length values will be treated as a time, rather than a duration which is what you want.  DAX doesn't have the concept of a duration.

Power Query does.  In PQ you can convert a time column to text, and then convert that to a duration.

Duplicate your data table, you can then group the rows by the Name and SUM the durations (note that I didn't enter the data exactly the same as yours so these Late Durations totals will be different to yours)

You've now got 2 tables.  One with the original data and one with the total late duration calculation.  Load these to PBI, create a relationship between the 2 based on Name.

This measure will give the Late Count

Count Lates = CALCULATE(COUNTROWS('DataTable2'), FILTER(ALL('DataTable2'), 'DataTable2'[Name] = SELECTEDVALUE('DataTable'[Name])))

Regards

Phil

[Lakka00]

Hi, I ave tried this but I get an error in the sum or duration column. 

I may be wrong but it doesn't seem to be converting to duration.

[PhilipTreacy]

@Lakka00 

You can't add times.  You need to convert the time column to text and then convert to duration. 

Did you check my example file to see what I did?

Phil

[Lakka00]

Hi, yes sorry. I am fairly new to this, I have followed the steps but I keep getting errors. I don't fully undertsnad the relationshiop access, But I managed to get it linked, however, it is in decimal form and the data is wrong, some peoples data is completely missing. Is there anyway you could break it down abit more so I can see where I am going wrong.

[PhilipTreacy]

@Lakka00 

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

[Lakka00]

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

[PhilipTreacy]

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

[Lakka00]

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. 

[v-yilong-msft]

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.

[Uzi2019]

Hi @Lakka00 

You can simply take your Late_length column change aggragation to count.

In your table it will be just Employee name and Count(Late_length) 

I hope I answered your question!

[Lakka00]

Hi, thank you but the 2nd column is the count of lates, I need the 3rd column to be the total amount of time late, for example, the first person was late 3 times, column 2 should show 3 lates and the last column should show 00:07:32 (Total time late of those 3 occasions)

[Uzi2019]

hi @Lakka00 

Can you try this dax present in the post.
https://community.fabric.microsoft.com/t5/Desktop/Working-with-time-duration/m-p/3674090

Or try this Dax

SumTalkTime =

VAR TotalSeconds=SUMX('Table Name',HOUR('Table Name'[Column])*3600+MINUTE('Table Name'[Column])*60+SECOND('Table Name'[Column]))
VAR Days =TRUNC(TotalSeconds/3600/24)
VAR Hors = TRUNC((TotalSeconds-Days*3600*24)/3600)
VAR Mins =TRUNC(MOD(TotalSeconds,3600)/60)
VAR Secs = MOD(TotalSeconds,60)
return IF(DAYS=0,"",IF(DAYS>1,DAYS&"days ",Days&"day"))&IF(Hors<10,"0"&Hors,Hors)&":"&IF(Mins<10,"0"&Mins,Mins)&":"&IF(Secs<10,"0"&Secs,Secs)

I hope I answered your question!