Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX formula calculating consecutive days

Hello,   I need help creating a measure to calculate consecutive days.   The objective is to identify the FIRST date(s) at which a student has NOT attended school for 5 or more consecutive calend...
Need Help
  • v-diye-msft's avatar
    v-diye-msft
    6 years ago

    Hi Anonymous 

     

    You can use this measure:

    Measure = var a = CALCULATE(MAX('Attendance Table'[Date]),FILTER(ALL('Attendance Table'),[AttendanceValue]="Attended"&&[Date]<MAX('Attendance Table'[Date])))
Return
IF(MAX('Attendance Table'[AttendanceValue])="Attended","null",IF(ISBLANK(a)&&MAX('Attendance Table'[AttendanceValue])="Absent",DATEDIFF(DATE(2019,8,30),MAX('Attendance Table'[Date]),DAY),DATEDIFF(a,MAX('Attendance Table'[Date]),DAY)))

     

    Pbiz attached,

kentyler's avatar
kentyler
Icon for Solution Sage rankSolution Sage
6 years ago

What if you convert your absent/attended column into 1's and 0's. Then you could add for each day the previous 5 days values, and if it was 5 then you'd have the students you want.