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 fro...
  • 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

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

JHart91's avatar
JHart91
Frequent Visitor
3 years ago

Hi Aniya,

 

Many thanks for your solution - I have added your flag / measure in and it seems to be working however what I have noticed is that the measure is displaying the maximum number of consecutive days absent regarless of the date that that particular student was last absent. 

 

What I am looking for is a way of counting students that are only absent on the current date and then display a count of the number of consecutive days from that point (inclusive of the most recent day's absence), and then to reset and display as 0 the next time that the student is present if that makes sense.

 

Thank you again for all your help and support.

Jamie