Forum Discussion
Counting Most Recent Rate of Consecutive Absence
Super UserJHart91 , 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