Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 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
    7 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
7 years ago

Here is a visual to further outline the issue with the measure. There are now 3 students in the table. If we look at Jane Doe's record where the [Date] value is "Sunday, September 15, 2019", you will see she has an "Absent" value on this record. On this record, the [ConsecutiveDaysMeasure] should be looking for Jane Doe's last "Attended" value prior to 9/15/19 and then calculate the difference in days. Jane Doe's last "Attended" record, prior to 9/15/19, was 9/5/19. So, the [ConsecutiveDaysMeasure] should be calculating 10 days. Instead, it appears to be referencing the last Attended record for ANY employee. In this case, the last attended date, prior to 9/15/19 was on 9/12/19 (for student Ken Stewart), so the measure is calculating 3 days. How do I get the measure to only apply to each individual student?

 

 

 

 

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 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!

    • Anonymous's avatar
      Anonymous
      Not applicable
      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)))))