DAX formula calculating consecutive days

[Anonymous]

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 calendar days. There are two tables which are linked (Enrollment and Attendance). The ‘consecutive non-attended days’ measure should be based off of (1) the absent date, and (2) the later of the student’s enrollment date and latest attended date (prior to the absent date in question).

The first two tables represent an example of the raw data.

The third table includes what I need the desired measure to be.

The fourth table is the desired end-result.

EnrollmentTable
StudentName	Enrollment Date
Jane Doe	8/30/2019

Attendance Table
StudentName	Date	AttendanceValue
Jane Doe	9/2/2019	Absent
Jane Doe	9/3/2019	Attended
Jane Doe	9/4/2019	Absent
Jane Doe	9/5/2019	Attended
Jane Doe	9/6/2019	Absent
Jane Doe	9/9/2019	Absent
Jane Doe	9/10/2019	Absent
Jane Doe	9/11/2019	Absent
Jane Doe	9/12/2019	Absent
Jane Doe	9/15/2019	Absent
Jane Doe	9/16/2019	Attended
Jane Doe	9/17/2019	Absent
Jane Doe	9/24/2019	Absent

Calculated Consecutive Non-Attended Days
StudentName	Date	AttendanceValue	Consecutive Non-Attended Days
Jane Doe	9/2/2019	Absent	3
Jane Doe	9/3/2019	Attended	null
Jane Doe	9/4/2019	Absent	1
Jane Doe	9/5/2019	Attended	null
Jane Doe	9/6/2019	Absent	1
Jane Doe	9/9/2019	Absent	4
Jane Doe	9/10/2019	Absent	5
Jane Doe	9/11/2019	Absent	6
Jane Doe	9/12/2019	Absent	7
Jane Doe	9/15/2019	Absent	10
Jane Doe	9/16/2019	Attended	null
Jane Doe	9/17/2019	Absent	1
Jane Doe	9/24/2019	Absent	8

Desired End Result
StudentName	Date	AttendanceValue	Consecutive Non-Attended Days
Jane Doe	9/10/2019	Absent	5
Jane Doe	9/24/2019	Absent	8

Any guidance would be appreciated.

Thanks,

John

[v-diye-msft · Accepted solution]

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]

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]

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]

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

[Anonymous]

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]

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)))))

[kentyler]

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.