Forum Discussion
DAX formula calculating consecutive days
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,
Community SupportHi 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?
Here are the images, blown up a bit...
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!