Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Lerager
Frequent Visitor

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
1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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

Cthulhu - Microsoft Power BI Community



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

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

Cthulhu - Microsoft Power BI Community



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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

@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

@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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.