Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Count consecutive days, starting over if not consecutive every day

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:

DateNameStatusCountNote
01-01-2022Name1Missed 01-01-20222 = 1. jan 2022
01-01-2022Name2Missed  
01-01-2022Name3Missed  
01-01-2022Name4Failed  
01-01-2022Name5Failed  
02-01-2022Name6Missed  
02-01-2022Name1Missed  
02-01-2022Name3Missed  
02-01-2022Name7Missed  
03-01-2022Name1Missed  
03-01-2022Name2Missed  
03-01-2022Name3MissedMissed 3 days consecutive 
03-01-2022Name7Missed  
04-01-2022Name6Missed  
04-01-2022Name7Missed  
04-01-2022Name5Missed  
04-01-2022Name1MissedMissed 4 days consecutive 
05-01-2022Name1Failed  
05-01-2022Name2Missed  
05-01-2022Name3Missed  
05-01-2022Name7Missed  
06-01-2022Name6Missed  
06-01-2022Name7MissedMissed 4 day consecutive 
06-01-2022Name5Missed  
06-01-2022Name1FailedFailed 2 days consecutive 
06-01-2022Name2MissedMissed 2 days consecutive 

 

Overview:

Name14/1 Missed 4 days consecutive
Name16/1 Failed 2 days consecutive
Name2 0 days consecutive
Name2 6/1 Missed 2 days consecutive
Name33/1 Missed 3 days consecutive
Name40 days consecutive
Name50 days consecutive
Name60 days consecutive
Name76/1 Missed 5 days consecutive
Need Help
Tips and Tricks

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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