Counting Most Recent Rate of Consecutive Absence

[JHart91]

Hi all,

I have a dataset that tracks the absence rate of students in my school and I am attempting to calculate the number of consecutive days of absence (this includes if a students is absent on the current day, then their count would be 1).

Each day is counted twice (i.e. there is an AM and a PM registration slot) so if a student is absent for only half a day I would like the counter to reset to 0. 

Some kind souls have already helped me with this measure (see below) but at the moment it only returns the highest occuring number of consecutive days for each student as opposed to the most recent. 

In essence I only want it to show how many students are absent on the most recent date and for those students, how many days they have been off since their last day in school.

Flag = 
var _cur_date = ('All attendance'[Date])
var _id = ('All attendance'[UPN])
var _t = SELECTCOLUMNS( FILTER('All attendance','All attendance'[UPN]= _id && 'All attendance'[Date]=_cur_date), "Statistical value", 'All attendance'[Statistical value])
return
IF( {"Absent"} in _t , 1, 0) 

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

 For reference 'UPN' is the unique reference number for each student.

Thanks in advance!!

Jamie

[amitchandak]

@JHart91 , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Check if this can help

https://community.powerbi.com/t5/Community-Blog/Power-BI-Continuous-Streak-With-One-Day-Break/ba-p/1...

Continuous streak : https://youtu.be/GdMcwvdwr0o

[JHart91]

Good morning,

The table below shows what the current data looks like with some examples - it is currently set to refresh each day:

UPN	Date	Description	Statistical Value
123	16/1/23	Morning	Present
123	16/1/23	Afternoon	Present
123	15/1/23	Morning	Present
123	15/1/23	Afternoon	Present
124	16/1/23	Morning	Absent
124	16/1/23	Afternoon	Absent
125	16/1/23	Morning	Present
125	15/1/23	Morning	Absent
125	15/1/23	Afternoon	Absent
125	14/1/23	Morning	Absent
125	14/1/23	Afternoon	Absent
126	16/1/23	Morning	Absent
126	15/1/23	Morning	Absent
126	15/1/23	Afternoon	Absent
126	14/1/23	Morning	Absent
126	14/1/23	Afternoon	Present

In terms of output from the examples I have put above I was hoping it would look something like this:

UPN	#Consecutive Days Absent	Last Date Present
123	0	16/1/23
124	1	15/1/23
125	0	16/1/23
126	2	14/1/23

So using the above examples to illustrate, student 124 is absent on the current day (but was here the day before) so his count is 1, 125 had 2 days off previously but he is now present on the current day so his count should return to 0 and 126 was in for the afternoon on the 14th so his count should be at 2 as well.

Hope this makes sense.

Thanks,

Jamie