Forum Discussion

JHart91's avatar
JHart91
Frequent Visitor
3 years ago
Solved

Calculating Consecutive Days of Absence

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 IDNameDateRoll CallMark
120Jane23/10/2022MorningPresent
120Jane23/10/2022AfternoonPresent
120Jane24/10/2022MorningPresent
120Jane24/10/2022AfternoonAbsent
120Jane25/10/2022MorningAbsent
120Jane25/10/2022AfternoonAbsent
120Jane26/10/2022MorningAbsent
120Jane26/10/2022AfternoonAbsent
121Colin23/10/2022MorningAbsent
121Colin23/10/2022AfternoonAbsent
121Colin24/10/2022MorningPresent
121Colin24/10/2022AfternoonPresent
121Colin25/10/2022MorningPresent
121Colin25/10/2022AfternoonPresent
121Colin26/10/2022MorningPresent
121Colin26/10/2022AfternoonPresent
122Ethan23/10/2022MorningPresent
122Ethan23/10/2022AfternoonPresent
122Ethan24/10/2022MorningAbsent
122Ethan24/10/2022AfternoonAbsent
122Ethan25/10/2022MorningPresent
122Ethan25/10/2022AfternoonPresent
122Ethan26/10/2022MorningAbsent
122Ethan26/10/2022AfternoonAbsent

 

Required Output:

Student IDNameDateConsecutive Days Absent
120Jane26/10/20223
121Colin26/10/20220
122Ethan26/10/2022

1

 

 

Any help would be greatly appreciated!

 

Thank you in advance,

Jamie

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    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

4 Replies

  • ppm1's avatar
    ppm1
    Icon for Solution Sage rankSolution Sage
    3 years ago

    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's avatar
      JHart91
      Frequent Visitor
      3 years ago

      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's avatar
        v-yueyunzh-msft
        Icon for Community Support rankCommunity Support
        3 years ago

        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