Calculate the quantity of consecutive loss per hour group

[Mp1977]

I’m trying to calculate the quantity of consecutive loss per hour group, per day, and the max consecutive loss per hour group.

I have tried to adapt some formulas for consecutive win and consecutive loss, but all of them accumulate for the whole period of time.

Can someone help me or point me to the right direction?

timestamp	WinLoss	       Hour Group
03/05/2020 01:39	Win	01:00-02:00
03/05/2020 01:39	Win	01:00-02:00
03/05/2020 01:39	Win	01:00-02:00
03/05/2020 01:40	Win	01:00-02:00
03/05/2020 01:40	Win	01:00-02:00
03/05/2020 01:43	Win	01:00-02:00
03/05/2020 01:44	Loss	01:00-02:00
03/05/2020 01:44	Win	01:00-02:00
03/05/2020 01:44	Loss	01:00-02:00
03/05/2020 01:45	Loss	01:00-02:00
03/05/2020 01:45	Loss	01:00-02:00
03/05/2020 01:47	Win	01:00-02:00
03/05/2020 01:47	Win	01:00-02:00
03/05/2020 01:48	Win	01:00-02:00
03/05/2020 01:48	Win	01:00-02:00
03/05/2020 01:48	Loss	01:00-02:00
03/05/2020 01:49	Win	01:00-02:00
03/05/2020 01:49	Win	01:00-02:00
03/05/2020 01:50	Win	01:00-02:00
03/05/2020 01:50	Win	01:00-02:00
03/05/2020 01:50	Loss	01:00-02:00
03/05/2020 01:51	Win	01:00-02:00
03/05/2020 01:51	Win	01:00-02:00
03/05/2020 01:52	Win	01:00-02:00
03/05/2020 01:52	Loss	01:00-02:00
03/05/2020 01:53	Win	01:00-02:00
03/05/2020 01:53	Win	01:00-02:00
03/05/2020 01:54	Loss	01:00-02:00
03/05/2020 01:54	Win	01:00-02:00
03/05/2020 01:54	Win	01:00-02:00
03/05/2020 01:55	Win	01:00-02:00
03/05/2020 01:55	Win	01:00-02:00
03/05/2020 01:56	Win	01:00-02:00
03/05/2020 01:56	Win	01:00-02:00
03/05/2020 01:56	Win	01:00-02:00
03/05/2020 01:56	Win	01:00-02:00
03/05/2020 01:57	Win	01:00-02:00
03/05/2020 01:57	Loss	01:00-02:00
03/05/2020 01:58	Win	01:00-02:00
03/05/2020 01:58	Loss	01:00-02:00
03/05/2020 01:58	Loss	01:00-02:00
03/05/2020 01:59	Win	01:00-02:00
03/05/2020 01:59	Loss	01:00-02:00
03/05/2020 02:00	Win	02:00-03:00
03/05/2020 02:00	Win	02:00-03:00
03/05/2020 02:01	Win	02:00-03:00
03/05/2020 02:01	Win	02:00-03:00
03/05/2020 02:02	Loss	02:00-03:00
03/05/2020 02:02	Win	02:00-03:00
03/05/2020 02:03	Loss	02:00-03:00
03/05/2020 02:03	Win	02:00-03:00
03/05/2020 02:04	Win	02:00-03:00
03/05/2020 02:04	Win	02:00-03:00
03/05/2020 02:04	Win	02:00-03:00
03/05/2020 02:05	Win	02:00-03:00
03/05/2020 02:06	Win	02:00-03:00
03/05/2020 02:06	Win	02:00-03:00
03/05/2020 02:06	Win	02:00-03:00
03/05/2020 02:07	Win	02:00-03:00
03/05/2020 02:07	Win	02:00-03:00
03/05/2020 02:07	Win	02:00-03:00
03/05/2020 02:08	Win	02:00-03:00
03/05/2020 02:08	Loss	02:00-03:00
03/05/2020 02:09	Win	02:00-03:00
03/05/2020 02:09	Win	02:00-03:00
03/05/2020 02:10	Win	02:00-03:00
03/05/2020 02:10	Win	02:00-03:00
03/05/2020 02:10	Loss	02:00-03:00
03/05/2020 02:11	Win	02:00-03:00
03/05/2020 02:11	Win	02:00-03:00
03/05/2020 02:11	Loss	02:00-03:00
03/05/2020 02:12	Win	02:00-03:00
03/05/2020 02:12	Win	02:00-03:00
03/05/2020 02:12	Loss	02:00-03:00
03/05/2020 02:13	Win	02:00-03:00
03/05/2020 02:13	Win	02:00-03:00
03/05/2020 02:14	Loss	02:00-03:00
03/05/2020 02:14	Win	02:00-03:00
03/05/2020 02:15	Win	02:00-03:00
03/05/2020 02:15	Win	02:00-03:00
03/05/2020 02:15	Loss	02:00-03:00
03/05/2020 02:16	Win	02:00-03:00
03/05/2020 02:17	Win	02:00-03:00
03/05/2020 02:17	Loss	02:00-03:00
03/05/2020 02:17	Win	02:00-03:00
03/05/2020 02:18	Win	02:00-03:00
03/05/2020 02:18	Win	02:00-03:00
03/05/2020 02:19	Win	02:00-03:00
03/05/2020 02:19	Win	02:00-03:00
03/05/2020 02:19	Win	02:00-03:00
03/05/2020 02:20	Win	02:00-03:00
03/05/2020 02:20	Win	02:00-03:00
03/05/2020 02:20	Loss	02:00-03:00
03/05/2020 02:21	Win	02:00-03:00
03/05/2020 02:22	Win	02:00-03:00
03/05/2020 02:22	Win	02:00-03:00
03/05/2020 02:22	Loss	02:00-03:00
03/05/2020 02:23	Loss	02:00-03:00
03/05/2020 02:23	Win	02:00-03:00
03/05/2020 02:24	Win	02:00-03:00
03/05/2020 02:24	Win	02:00-03:00

Thank you in advance

[Anonymous]

Please define what is "the quantity of consecutive loss" in any setting and give good, small examples of what you want to really calculate. Currently, it does not seem to be clear what the end goal is.

Thanks.

Best
D

[stevedep]

For this one I would recommend to solve it (mostly) in Power Query:

1. Add an index column. 

2. Replicate the table and add a new column (index -1)

3. Join the 2 tables on Index and Index -1

4. compare the winloss of both tables, true is a consecutive value. 

5. slice and dice the data to your linking in the front-end. 

Power BI file available here. 

Please mark as solution if this works for you. Kudo's / thumbs up for the effort are appreciated.

Kind regards, Steve.