Contious events time cutoffs

[hirwa]

Hello guys,

I have a question and I am not sure if it has been asked before somewhere else, but please give it a try. I have a continous event. For example, the cutoff reporting time is midnight - to make it easier. Then there is an event that start 23:40:50 and finishes 00:34:40. I was able to write a code that can report this event from 23:40:50 and finishes 23:59:59 for yesterday and works fine. Now the question to you guys, how do I bring the left time frame from 00:00:00 to 00:40:50 of this event to the next day. Suppose that a sensor is creating different events no stop and we want to report only the events from 00:00:00 to 23:59:59.

Thank you in advance!

H.

[amitchandak]

@hirwa , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

refer if this can help

Continuous streak : https://youtu.be/GdMcwvdwr0o

[hirwa]

Hi @amitchandak ,

Sorry, I have been very busy and many thank for your reply.

I am not quite sure if I can upload a file here.

Here is a sample table below (Not enough), but it will give you an idea. There are 7 activities done by a machine A, and each activity is recorded in second. We are asked to provide the activities happend in 24 hours window - their total hours/min/sec for each activity. Let's say from 24/03/2023 06:00 AM to 25/03/2023 06:00 AM the next day.

Now if the activity 7 starts 25/03/2023 05:55:20 AM to 25/03/2023 06:14:10 AM, we have to make sure that the cut-off for 24 hours is 25/03/2023 06:00:00 AM, which means activity 7 would be from 25/03/2023 05:55:20 AM to 25/03/2023 06:00:00 AM for this reporting of 24 hrs. The result would look like (in chart) with 24 hrs, distributted with different activities total in 24 hrs. from 6 AM to 6 AM the next day.

24 hrs       

act1 = 2 hrs

act4 = 3 hrs

act3 = 7hrs

act5 = 1hrs

act2 = 2.5 hrs

act6 = 1.2 hrs

...

machine	start_date	end_date	activity	running_time (sec)
A	24/01/2023 14:35:20	24/01/2023 14:35:24	Activity 1	4
A	24/01/2023 14:35:24	24/01/2023 14:35:51	Activity 2	27
A	24/01/2023 14:35:51	24/01/2023 14:35:54	Activity 2	3
A	24/01/2023 14:35:54	24/01/2023 14:36:04	Activity 2	10
A	24/01/2023 14:36:04	24/01/2023 14:40:35	Activity 2	271
A	24/01/2023 14:40:35	24/01/2023 14:40:36	Activity 1	1
A	24/01/2023 14:40:36	24/01/2023 14:41:10	Activity 2	34
A	24/01/2023 14:41:10	24/01/2023 14:46:00	Activity 2	290
A	24/01/2023 14:46:00	24/01/2023 14:46:02	Activity 1	2
A	24/01/2023 14:46:02	24/01/2023 14:46:50	Activity 2	48
A	24/01/2023 14:46:50	24/01/2023 14:46:52	Activity 2	2
A	24/01/2023 14:46:52	24/01/2023 14:47:19	Activity 2	27
A	24/01/2023 14:47:19	24/01/2023 14:50:51	Activity 2	212
A	24/01/2023 14:50:51	24/01/2023 14:50:52	Activity 1	1
A	24/01/2023 14:50:52	24/01/2023 14:51:58	Activity 2	66
A	24/01/2023 14:51:58	24/01/2023 14:56:19	Activity 2	261
A	24/01/2023 14:56:19	24/01/2023 14:56:24	Activity 1	5
A	24/01/2023 14:56:24	24/01/2023 14:59:05	Activity 2	161
A	24/01/2023 14:59:05	24/01/2023 15:02:17	Activity 2	192
A	24/01/2023 15:02:17	24/01/2023 15:02:20	Activity 1	3
A	24/01/2023 15:02:20	24/01/2023 15:04:14	Activity 2	114
A	24/01/2023 15:04:14	24/01/2023 15:04:20	Activity 2	6
A	24/01/2023 15:04:20	24/01/2023 15:05:53	Activity 2	93
A	24/01/2023 15:05:53	24/01/2023 15:09:27	Activity 2	214
A	24/01/2023 15:09:27	24/01/2023 15:09:29	Activity 1	2
A	24/01/2023 15:09:29	24/01/2023 15:10:25	Activity 2	56
A	24/01/2023 15:10:25	24/01/2023 15:10:35	Activity 2	10
A	24/01/2023 15:10:35	24/01/2023 15:12:22	Activity 2	107
A	24/01/2023 15:12:22	24/01/2023 15:15:26	Activity 2	184
A	24/01/2023 15:15:26	24/01/2023 15:15:27	Activity 1	1
A	24/01/2023 15:15:27	24/01/2023 15:19:23	Activity 2	236
A	24/01/2023 15:19:23	24/01/2023 15:22:40	Activity 2	197
A	24/01/2023 15:22:40	24/01/2023 15:22:42	Activity 1	2
A	24/01/2023 15:22:42	24/01/2023 15:26:59	Activity 2	257
A	24/01/2023 15:26:59	24/01/2023 15:30:22	Activity 2	203
A	24/01/2023 15:30:22	24/01/2023 15:30:27	Activity 1	5
A	24/01/2023 15:30:27	24/01/2023 15:31:33	Activity 2	66
A	24/01/2023 15:31:33	24/01/2023 15:35:12	Activity 2	219
A	24/01/2023 15:35:12	24/01/2023 15:35:16	Activity 1	4
A	24/01/2023 15:35:16	24/01/2023 15:36:00	Activity 2	44
A	24/01/2023 15:36:00	24/01/2023 15:40:50	Activity 1	290
A	24/01/2023 15:40:50	24/01/2023 15:42:53	Activity 2	123