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,

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Here are the images, blown up a bit...

Anonymous's avatar
Anonymous
Not applicable
6 years ago

I believe I figured it out. By adding a '[StudentID]=Max([StudentID]' condition to the measure, the measure is now calculating correctly. Again, thank you very much for your help in creating the measure!

  • JHart91's avatar
    JHart91
    Frequent Visitor
    3 years ago

    Hi I appreciate you posted this three years ago but I am now looking to achieve the same results you are - my knowledge of DAX is relatively limited but I essentially copied over your formatting but it hasn't worked for me. 

     

    Where did you add the condition for the StudentID = MaxStudentID part?

     

    Also when I try to run the measure it is telling me there is a syntax error due to a miscelanoues ')' somewhere but I cannot for the life of me seem to see where! Any help would be greatly appreciated...Thanks!

     

    Measure = var a = CALCULATE(MAX('All_attendance'[Date]),FILTER(ALL('All_attendance'),[Statistical value] = "Present" &&[UPN]=MAX('All_attendance'[UPN])&&[Date]<MAX('All_attendance'[Date])))
    Return
    IF(MAX('All_attendance'[Statistical value])="Present", "null", IF(ISBLANK(a)&&MAX('All_attendance'[Statistical value])="Absent", DATEDIF(DATE, (5, 9, 2022),MAX('All_attendance'[Date], DAY), DATEDIFF(a, MAX('All_attendance'[Date], DAY)))))