Forum Discussion
Counting Most Recent Rate of Consecutive Absence
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
2 Replies
- amitchandak
Super User
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
Continuous streak : https://youtu.be/GdMcwvdwr0o
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
