Forum Discussion

metaway_ti's avatar
metaway_ti
New Member
2 years ago
Solved

Calculating Consecutive Days of Students Absence

Hi,

I have a table that contains the registers of the students presence and absence accordingly with the school, group, period of the class, and date of the class. So, in this table there is a columm that identificate if the student was present or not in the class. 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 two or more days from the current date. However, I need that, if the students that has absences for consecutive days will return to class the calculation of the amount of consecutive days of absences start over. 

Any help would be greatly appreciated!

Thank you in advance,
Danielle

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi metaway_ti ,

    I see that lbendlin  has asked you for data again. Based on your reply, I have some ideas that I want to share.

    Below is my table:

     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)

     

    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 ))

     

    The final output is shown in the following figure:

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

    Best Regards,

    Xianda Tang

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

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    Sounds good. What is your question?  Do you have sample data?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi metaway_ti ,

    I see that lbendlin  has asked you for data again. Based on your reply, I have some ideas that I want to share.

    Below is my table:

     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)

     

    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 ))

     

    The final output is shown in the following figure:

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

    Best Regards,

    Xianda Tang

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