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

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

    • 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