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

Hello Dina Ye,

Thank you very much for the measure! It works beautifully in the test table. However, when I applied it to the full database attendance table, the measure did not produce the desired result. I believe the issue is that my full table contains lots of different students. For a given student, the measure appears to be factoring in the attendance values of all the other students in the table. When I hardcode a specific/unique studentid (e.g. studentid = "ao89e8d") into the filter on the measure, then the measure produces the correct results. However, if I create a report with a report filter for studentid="ao89e8d" (using the original measure), again, the measure appears to be referencing all the students attendance values (not just the desired student) and the measure does not produce the correct results. Typically I would be running a report to pull many different students, so it is not feasible to hardcode individual student ids into the measure. So, how do I get the measure to evaluate at the studentid level?

 

Thanks,

 

John 

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