"Gaps&Islands" consecutive days in student absences

[ncraft]

I am working with a large table of student attendance data that spans mutlipel campuses and school years. 
There is a column for dates and student IDs, where each date entry represents a day the student was marked absent. 
I need a measure or a calculated column that can return whether or not an absense for a given student is consecutive, and for each consecutive absensce, what is the cumulative value of that absense. 
Here is a sample data set: I have highlighted the 3 student ids to show how they are not ordered

Here is an example of what the my desired result would look like

[Greg_Deckler · Accepted solution]

@ncraft Seems like a varation on Cthulhu or MTBF. Basically, compare the previous row defined by the student id and date. So, essentially, grab the student id and date in the current row. Identify the gaps. Count the number of consequetive rows (difference = 1) between the last gap and the current row. 

Here is the Link to Cthulhu: Cthulhu - Microsoft Power BI Community

I also recently published a detailed video on how to use it and how it works: Microsoft Hates Greg - Cthulhu! - YouTube

If you post sample data as text I can take a closer look.

[ncraft]

Hi @Greg_Deckler (or anyone else reading this who can help),
so request from buisness has added another layer to this problem and I was wondering if you could help me out. I have been scratching my head trying to modify your origional solution but I cant quite get it there.

On our data table that includes the "group" ([student_id])  and the "index" ([date]) there is now a third column [attendance_value] that we need to filter for. [attendance_value] is always equal to 1.0 or 0.5 and buisness does not want rows with value 0.5 to be part of the consecutive count. I can't perform a transform on the data table because the same table is also considered by other measures and visuals in the report. There are also ofcourse some important relationships in the modle between this table and others. I have tried adding an additional helper column but having cuthulhu reference another calcuated column seems to compound the calulation time and make it impossible to execute. I figure there there has got to be a way to correctly include the filter in the cuthulhu code. 
I tried setting it up like this:

VAR __group = CALCULATE(MAX('data'[student_id]), 'data'[attendance_value]=1)

and that produces the desired result in some cases but not all. 

Here is a sample data set where the [cuthulhu] column is a straight copy+paste of your origional solution. On this sample set, I have added a [desired_cuthulhu] my manualy entering the value in each cell for the outcome I am trying to get [cuthulhu] to return. 
*Note that the [student_id] column here is all of one kind intentionally. 

student_id	date	attendance_value	cuthuluh	desired_cuthulhu
269182	2/1/2021	1	1	1
269182	2/2/2021	1	2	2
269182	2/3/2021	1	3	3
269182	2/4/2021	1	4	4
269182	2/5/2021	1	5	5
269182	2/8/2021	0.5	1	1
269182	2/9/2021	1	2	1
269182	2/10/2021	1	3	2
269182	2/11/2021	1	4	3
269182	2/12/2021	1	5	4
269182	2/16/2021	0.5	1	1
269182	2/17/2021	1	2	1
269182	2/18/2021	0.5	3	1
269182	2/19/2021	1	4	1
269182	2/20/2021	1	5	2

[ncraft]

Wow @Greg_Deckler, this is exactly what I needed.
I didnt have to modify your solution at all. I plugged in [date] for [index] and [student_id] for [animal] and cthulhu worked perfectly.
Thank you so much for your hard work on this (even if it was years ago). Not only will this be valuble for my team, but I can also take some solice knowing I wasnt the only one that went nearly insane working on this problem 😄