DAX virtually create 2 tables base on multiple logique for retention anaylsis

[mlovejoy]

Hello all,

I want to create a Student retention analysis from the initial first term until the end of the curriculum.

I have 3 tables to do that which already have all the information I need. The only thing is create the cohort of New student that have started on the same Quarter and Year (Text). I have 3 possibilities but I m not able to make work any one of them >_<:

• Be able to put the first Intake Year of each student from the enrollment table in the student table based on Min Date starting, criteria = 1 and and student entry type = New student. From this I could do a related(Student Table (Intake Year) ) in the enrollment table.

• Directly add a column in my enrollment table that meet all the criteria. (each student ID gets the first Intake Year based on Min Date starting, Criteria = 1 and Entry Type = New student)

• Create a DAX measure that virtually create a table of unique student ID and have the Intake Year base on the same above criteria. And includes a count row to calculate the number of students.

Any tips? It looks fairly simple as the retention between years exists already in my data. I “just” need to virtually separate my data in New Student and Returning Student with the information of the Intake Year so I’ll be able to create this cohort as shown in the example . I can’t get it right

 

[amitchandak]

@mlovejoy , check the use of earlier to get the date

Power BI Cohort Analysis, Customer Retention %- https://youtu.be/qY1SDF1cwsg

Period Of Stay – Cohort Analysis: https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-3-Period-Of-Stay-Cohort-Anal...