Calculate average in certain timeperiod while looking for value above a number

[Anonymous]

Hi,

I'm trying to calculate the average of a column1 after binary column2 is 1. After that column2 turns 1, the column2 will roughly 30 minutes later increase its value from about 5 to about 10 for 6 hours. I need to calculate the average of that six hour window after the value has increased. This can happen multiple times in my dataset and I would like to find a way to identify each occurrence and see the average of each of them.

Does anyone have any good ideas on how to go about this?

Datetime	vol	binary
26.10.2019 00:10	4.2	0
26.10.2019 00:20	2.3	0
26.10.2019 00:30	4.5	0
26.10.2019 00:40	4.6	0
26.10.2019 00:50	5	0
26.10.2019 01:00	5.29	0
26.10.2019 01:10	5.68	0
26.10.2019 01:20	4.34	0
26.10.2019 01:30	4.32	0
26.10.2019 01:40	4.3	0
26.10.2019 01:50	4.28	0
26.10.2019 02:00	4.26	1
26.10.2019 02:10	4.55	1
26.10.2019 02:20	4.55	1
26.10.2019 02:30	4.55	1
26.10.2019 02:40	9.2	1
26.10.2019 02:50	10	1
26.10.2019 03:00	10	1
26.10.2019 03:10	10	1
26.10.2019 03:20	10	1
26.10.2019 03:30	10	1
26.10.2019 03:40	10	1
26.10.2019 03:50	10	1
26.10.2019 04:00	10	1
26.10.2019 04:10	10	1
26.10.2019 04:20	10	1
26.10.2019 04:30	10	1
26.10.2019 04:40	10	1
26.10.2019 04:50	10	1
26.10.2019 05:00	10	1
26.10.2019 05:10	10	1
26.10.2019 05:20	10	1
26.10.2019 05:30	10	1
26.10.2019 05:40	10	1
26.10.2019 05:50	10	1
26.10.2019 06:00	10	1
26.10.2019 06:10	10	1
26.10.2019 06:20	10	1
26.10.2019 06:30	10	1
26.10.2019 06:40	10	1
26.10.2019 06:50	10	1
26.10.2019 07:00	10	1
26.10.2019 07:10	10	1
26.10.2019 07:20	10	1
26.10.2019 07:30	10	1
26.10.2019 07:40	10	1
26.10.2019 07:50	10	1
26.10.2019 08:00	10	1
26.10.2019 08:10	10	1
26.10.2019 08:20	9.89	1
26.10.2019 08:30	10	1
26.10.2019 08:40	10	1
26.10.2019 08:50	4.34	0
26.10.2019 09:00	4.98	0
26.10.2019 09:10	4.96	0
26.10.2019 09:20	4.94	0
26.10.2019 09:30	4.92	0
26.10.2019 09:40	4.9	0
26.10.2019 09:50	4.88	0
26.10.2019 10:00	4.86	0
26.10.2019 10:10	4.84	0
26.10.2019 10:20	4.82	0
26.10.2019 10:30	4.8	0
26.10.2019 10:40	4.78	0
26.10.2019 10:50	4.76	0
26.10.2019 11:00	4.74	0
26.10.2019 12:00	4.72	0
26.10.2019 12:10	4.73	0
26.10.2019 12:20	4.74	0
26.10.2019 12:30	4.75	0
26.10.2019 12:40	4.76	0
26.10.2019 12:50	4.77	0
26.10.2019 13:00	4.78	0
26.10.2019 13:10	4.79	0
26.10.2019 13:20	4.8	0
26.10.2019 13:30	4.81	1
26.10.2019 13:40	4.8	1
26.10.2019 13:50	4.79	1
26.10.2019 14:00	4.78	1
26.10.2019 14:10	9.89	1
26.10.2019 14:20	9.9	1
26.10.2019 14:30	9.91	1
26.10.2019 14:40	9.92	1
26.10.2019 14:50	9.93	1
26.10.2019 15:00	9.94	1
26.10.2019 15:10	9.95	1
26.10.2019 15:20	9.96	1
26.10.2019 15:30	9.97	1
26.10.2019 15:40	9.98	1
26.10.2019 15:50	9.99	1
26.10.2019 16:00	10	1
26.10.2019 16:10	10.01	1
26.10.2019 16:20	10.02	1
26.10.2019 16:30	10.03	1
26.10.2019 16:40	10.04	1
26.10.2019 16:50	10.05	1
26.10.2019 17:00	10.06	1
26.10.2019 17:10	10.07	1
26.10.2019 17:20	10.08	1
26.10.2019 17:30	10.09	1
26.10.2019 17:40	10.1	1
26.10.2019 17:50	10.11	1
26.10.2019 18:00	10.12	1
26.10.2019 18:10	10.13	1
26.10.2019 18:20	10.14	1
26.10.2019 18:30	10.15	1
26.10.2019 18:40	10.16	1
26.10.2019 18:50	10.17	1
26.10.2019 19:00	10.18	1
26.10.2019 19:10	10.19	1
26.10.2019 19:20	10.2	1
26.10.2019 19:30	10.21	1
26.10.2019 19:40	10.22	1
26.10.2019 19:50	10.23	1
26.10.2019 20:00	10.24	1
26.10.2019 20:10	10.25	1
26.10.2019 20:20	4.65	0
26.10.2019 20:30	4.66	0
26.10.2019 20:40	4.67	0
26.10.2019 20:50	4.68	0
26.10.2019 21:00	4.69	0
26.10.2019 21:10	4.7	0
26.10.2019 21:20	4.71	0
26.10.2019 21:30	4.72	0
26.10.2019 21:40	4.73	0
26.10.2019 21:50	4.74	0
26.10.2019 22:00	4.75	0
26.10.2019 22:10	4.76	0
26.10.2019 22:20	4.77	0
26.10.2019 22:30	4.78	0
26.10.2019 22:40	4.79	0
26.10.2019 22:50	4.8	0
26.10.2019 23:00	4.81	0
26.10.2019 23:10	4.82	0
26.10.2019 23:20	4.83	0
26.10.2019 23:30	4.84	0
26.10.2019 23:40	4.85	0
26.10.2019 23:50	4.86	0

[AlB]

Hi @Anonymous 

Can you please:

1.  Show the sample data in text-tabular format instead of on a pic, s that it can be copied?

2. Show the expected result and how you would like to present it (in a visual, in an additional calculated column in the original table)?

Please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. A screen cap doesn't allow people to readily copy the data and run a quick test and thus decreases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  

[Anonymous]

Sorry, fixed now