Forum Discussion
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 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
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,
7 Replies
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.
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,
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
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?
