Count consecutive days, starting over if not consecutive every day

[Lerager]

Hi 

I get at list of jobs everyday, and I want to be able to see if a jobe have failed or is missed more than one day in a row. I have manualy create a short example, but I would like to know how to do this in Power BI, as I have over 1000 jobs.

I would to be able to show the counts on a date line, with the number of consecutive days of failurs or missed, but when there have been just one day without failure or missed the count should start over.

Data:

Date	Name	Status	Count	Note
01-01-2022	Name1	Missed		01-01-20222 = 1. jan 2022
01-01-2022	Name2	Missed
01-01-2022	Name3	Missed
01-01-2022	Name4	Failed
01-01-2022	Name5	Failed
02-01-2022	Name6	Missed
02-01-2022	Name1	Missed
02-01-2022	Name3	Missed
02-01-2022	Name7	Missed
03-01-2022	Name1	Missed
03-01-2022	Name2	Missed
03-01-2022	Name3	Missed	Missed 3 days consecutive
03-01-2022	Name7	Missed
04-01-2022	Name6	Missed
04-01-2022	Name7	Missed
04-01-2022	Name5	Missed
04-01-2022	Name1	Missed	Missed 4 days consecutive
05-01-2022	Name1	Failed
05-01-2022	Name2	Missed
05-01-2022	Name3	Missed
05-01-2022	Name7	Missed
06-01-2022	Name6	Missed
06-01-2022	Name7	Missed	Missed 4 day consecutive
06-01-2022	Name5	Missed
06-01-2022	Name1	Failed	Failed 2 days consecutive
06-01-2022	Name2	Missed	Missed 2 days consecutive

Overview:

Name1	4/1 Missed 4 days consecutive
Name1	6/1 Failed 2 days consecutive
Name2	0 days consecutive
Name2	6/1 Missed 2 days consecutive
Name3	3/1 Missed 3 days consecutive
Name4	0 days consecutive
Name5	0 days consecutive
Name6	0 days consecutive
Name7	6/1 Missed 5 days consecutive

[Greg_Deckler · Accepted solution]

@Lerager Sounds like a repeating counter with conditions, otherwise known as Cthulhu:

Cthulhu - Microsoft Power BI Community

[Lerager]

Hi again Greg

First thanks for the solution, it worked.

My new challenge is to only collect the highest number of a sequence in Cthulhu and also the larst date value together with the Cthulhu value.

Is that possible? 

So I'm not intereted in all the counts 1, 2, 3, 4 and 5, only 5 and the information related to this entry.

Regards Bent

[Lerager]

@Greg_Deckler 

Never hear of Cthulhu before, but as I read this I can't use it, as I don't have an "Index", I only have a date, that can have multiple entries of jobs.

It is not as easy as a counter where all jobs comes listed one by one.

Thanks

[Greg_Deckler]

@Lerager You can use either Index or Date, anything that defines "before". Here is another example where I believe dates were used. (3) Bride of Cthulhu! - Microsoft Power BI Community