Calculating Consecutive Days of Absence

[JHart91]

Hi,

I am trying to find a way of calculating the amount of consecutive days students are absent from school and return a table that only has students who have been absent for one or more days from the current date.

We have two statutory roll calls per day, one in the morning and one in the afternoon however I would like it to continue counting even if a student is absent for part of a day (i.e. present in the morning but absent in the afternoon). 

Each student has their own unique identifier and we also have studens who have multiple absences so I would only like it to report the most recent count of consecutive days. 

The example below is the raw data (set to automatically refresh overnight) and what I am looking for the output to look like.

Raw Data:

Student ID	Name	Date	Roll Call	Mark
120	Jane	23/10/2022	Morning	Present
120	Jane	23/10/2022	Afternoon	Present
120	Jane	24/10/2022	Morning	Present
120	Jane	24/10/2022	Afternoon	Absent
120	Jane	25/10/2022	Morning	Absent
120	Jane	25/10/2022	Afternoon	Absent
120	Jane	26/10/2022	Morning	Absent
120	Jane	26/10/2022	Afternoon	Absent
121	Colin	23/10/2022	Morning	Absent
121	Colin	23/10/2022	Afternoon	Absent
121	Colin	24/10/2022	Morning	Present
121	Colin	24/10/2022	Afternoon	Present
121	Colin	25/10/2022	Morning	Present
121	Colin	25/10/2022	Afternoon	Present
121	Colin	26/10/2022	Morning	Present
121	Colin	26/10/2022	Afternoon	Present
122	Ethan	23/10/2022	Morning	Present
122	Ethan	23/10/2022	Afternoon	Present
122	Ethan	24/10/2022	Morning	Absent
122	Ethan	24/10/2022	Afternoon	Absent
122	Ethan	25/10/2022	Morning	Present
122	Ethan	25/10/2022	Afternoon	Present
122	Ethan	26/10/2022	Morning	Absent
122	Ethan	26/10/2022	Afternoon	Absent

Required Output:

Student ID	Name	Date	Consecutive Days Absent
120	Jane	26/10/2022	3
121	Colin	26/10/2022	0
122	Ethan	26/10/2022	1

Any help would be greatly appreciated!

Thank you in advance,

Jamie

[ppm1]

Here is a measure expression that seems to work. Replace T3 with your actual table name throughout.

Consec Absences =
VAR vMaxdate =
    MAX ( T3[Date] )
VAR vSummary =
    SUMMARIZE (
        t3,
        T3[Student ID],
        T3[Date],
        T3[Mark],
        "cRows", COUNT ( T3[Student ID] )
    )
VAR vMaxPresent =
    MAXX ( FILTER ( vSummary, [cRows] = 2 && T3[Mark] = "Present" ), T3[Date] )
RETURN
    INT ( vMaxdate - vMaxPresent )

Pat

[JHart91]

Incredible thanks very much for your quick and helpful response!

Is it easy to change this to also include students who are absent on the most recent day so that it shows up on the table? i.e. if the date was the 16th December and that was their first day of absence that would count as day 1 on the consecutive absence count.

Thanks again!

Jamie

[v-yueyunzh-msft · Accepted solution]

Hi, @JHart91 

 According to your description, you want to "change this to also include students who are absent on the most recent day". For your sample data , the "Colin" student will return 1 . Right?

If this , here are the steps you can refer to :
(1)My test data is the same as yours.

(2)We can add a flag column like this in the table:

Flag = var _cur_date = [Date] 
var _id = [Student ID]
var _t =SELECTCOLUMNS( FILTER( 'Table','Table'[Student ID]=_id && 'Table'[Date] = _cur_date),"Mark" , [Mark])
return
IF( {"Absent"} in _t , 1,0) 

(3)We can add a measure :

Measure = var _t =  FILTER('Table' , 'Table'[Flag] =1)
var _max_1_date = MAXX(_t , [Date])
var _t2 = FILTER('Table','Table'[Flag]=0 && 'Table'[Date]< _max_1_date)
var _max_0_date = MAXX(_t2,[Date])
var _t3 = FILTER('Table', 'Table'[Date]<= _max_1_date && 'Table'[Date]>_max_0_date)
return
IF(_max_1_date = BLANK() , 0 , IF(_max_0_date=BLANK() , COUNTROWS(_t)/2 , COUNTROWS(_t3)/2 ))

Then we can put the field we need on the visual and we can meet your need:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[JHart91]

Hi Aniya,

Many thanks for your solution - I have added your flag / measure in and it seems to be working however what I have noticed is that the measure is displaying the maximum number of consecutive days absent regarless of the date that that particular student was last absent. 

What I am looking for is a way of counting students that are only absent on the current date and then display a count of the number of consecutive days from that point (inclusive of the most recent day's absence), and then to reset and display as 0 the next time that the student is present if that makes sense.

Thank you again for all your help and support.

Jamie