If there is no next row for an ID, the previous row needs to be counted

[JoSwinnen]

Hi all, 

a DAX question. So I have multiple rows per ID for each year. Each row represents a registration of a student. And if the student is not graduated yet there appears no in the row. If he graduates, like for instance 000001 after 3 years, there will appear yes. Now I want to know how many students did not graduated in a year AND did not register the next year. In the example, student 000002 did not graduated in 2020 and he didn't register in 2021. This student needs to be counted in 2020. Student 000003 needs to be counted in 2019. 

StudentID     Date    Graduated? 

000001           2019       No

000001           2020       No

000001           2021       Yes

000002           2019         No

000002           2020        No

000003           2019        No

000004           2019        No

000004          2020        Yes

Any ideas?

Thanks,

Jo 

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood the question correctly, but please check the below picture and the attached pbix file.

[tamerj1]

Hi @JoSwinnen 

is the Date column actually a date data type of just contains the year number (integer)?

[JoSwinnen]

It just contains the year number.