Student Retention: Term-by-Term, Fall-to-Fall, Year-by-Year

[BlueTeam]

Trying to figure out how to accurately measure retention of students from Term-to-Term, Fall-to-Fall and Year-by-Year and am failing.  The issue around applying consistant filters.

Goal:

Measure retention as described above AND be able to consistently apply the same cohort filter(s) to subsequent term/year enrollment data.  I'd like to add additional filters such as Declared Major, etc. in the future.

Example:

Cohort=2017 Academic Year, Term=2, Enrollment Status=Full-Time

Results:

Cohort is accurate but subsequent term data are not.  Subsequent term data may be a mix of Full-Time, Part-Time and Non-Credit.

Currently I'm using two tables (cohort and subsequent enrollment data) but would like to use a single table containing all enrollment data over a 5-year period.

Thanks everyone!

[v-shex-msft]

Hi @BlueTeam ,

Can you please share some sample data with expect result to help us clarify your requirement?

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

[BlueTeam]

Sure.  Here is a simplified example of enrollment data.  This would be an example of all enrollment history.  I would like to define the starting cohort by filters (Academic Year, Academic Term, Enrollment Status, etc.) and apply the same or different filters on the resulting retention side.  Obviously the retention academic period (YearTerm) must be greater than the cohort YearTerm.  Hope that make sense.  Thanks!

Academic Year	Academic Term	YearTerm	id	Enrollment Status	Credit_Hours
2017	4	20174	1000	Full-Time	12
2016	4	20164	1000	Full-Time	12
2017	3	20173	1000	Part-Time	4
2017	2	20172	1001	Full-Time	12
2017	3	20173	1001	Full-Time	12
2017	4	20174	1001	Part-Time	9
2018	4	20184	1001	Part-Time	9
2018	2	20182	1001	Part-Time	9
2016	4	20164	1001	Part-Time	4
2016	3	20163	1001	Part-Time	4
2016	2	20162	1001	Part-Time	4
2018	3	20183	1001	Part-Time	9
2017	4	20174	1002	Full-Time	13
2016	2	20162	1002	Full-Time	15
2016	3	20163	1002	Full-Time	17
2016	4	20164	1002	Full-Time	18
2017	2	20172	1002	Full-Time	17
2017	3	20173	1002	Full-Time	17
2019	4	20194	1003	Non-Credit	0
2019	3	20193	1003	Non-Credit	0
2016	4	20164	1004	Non-Credit	0
2016	3	20163	1005	Non-Credit	0
2016	3	20163	1006	Part-Time	8
2017	2	20172	1006	Part-Time	4
2019	4	20194	1007	Part-Time	4
2019	2	20192	1007	Part-Time	4
2019	3	20193	1007	Part-Time	4

[Ashish_Mathur]

Hi,

Please show the exact result you are expecting on this sample data.

[BlueTeam]

Here is a quick example (not final look).  On left is the starting cohort where I can apply filers to Enrollment Status, YearTerm, etc..  The graph shows retention by subsequent term, but it shows all retention, regardless of Enrollment Status in the joined table.  For example, If I wanted to know how many non-credit students were enrolled for Fall (yearterm 20192) who subsequently enrolled part-time, I would select the Part-Time filter on the right (enrollment table).  Unfortunately I get wonky results (second picture).  As you can see, Term 2 completely disappears and I end up with completely inaccurate results.

In this example, I only show one year but the goal is to show year-to-year in another graph with similar filters (YearTerm to YearTerm).  For example, Fall 2017-18 to Fall 2018-19 and break that out by starting Enrollment Status and ending Enrollment Status over the entire timeline.  So 1,000 students start out as full-time, we want to track not only their retention but also their enrollment status term by term, year by year.  Make sense?

Thanks again!

Photo 1: Retention by Term for Non-Credit Students

Photo 2: Retention by Term for Non-Credit to Full-Time

[BlueTeam]

Here is the sample tables and relationship I've started with.

[Ashish_Mathur]

Hi,

It would be ideal if you could show the result in a Table.  Once the Table is ready, we can build whatever visual we want.  Also, why are there 2 source tables - there should be one one source Table.

[BlueTeam]

Yes, I would prefer to use a single table with all enrollment history.  Below is a made up table of what I'm looking to do.

1. The starting cohort in this case is Fall 2017-18, Term 2.  The table shows the entire academic year plus the next fall term (Fall 2018-19, Term 2).  I've used concatenated Academic Year and Academic Term in the past to perform this analysis in SQL so for example, the cohort above would have a value of 20182 and the next academic fall term would be 20192.

The goal here is not not just calculate retention but to also get into the weeds of how students swirl between full-time, part-time and non-credit enrollment.  In the first example, 1050 full-time students started in Fall 2017-18, Term 2 (starting cohort).  Subsequently, only 985 retained that status in Winter Term, 872 in Spring and 646 the next Fall Term.

Fall 2017-18	Winter 2017-18	Spring 2017-18	Fall 2018-19
Term 2	Term 3	Term 4	Term 2	Enrollment Status
1050	985	872	646	Full-Time to Full-Time
1050	212	202	98	Full-Time to Part-Time
1050	10	5	0	Full-Time to Non-Credit
602	454	398	125	Part-Time to Part-Time
602	86	51	35	Part-Time to Full-Time
602	12	3	1	Part-Time to Non-Credit
1805	1562	1301	840	Non-Credit to Non-Credit
1805	57	43	12	Non-Credit to Part-Time
1805	15	15	7	Non-Credit to Full-Time

[Ashish_Mathur]

Hi,

I think i can help here.  Share that single table from where you want to generate your expected result.  Based on that Table you share, please also share the exact figures you want to see in the Cohort table so that i can tally my answers with yours.

[BlueTeam]

Here is the sample table of enrollment data by enrollment status.

Sample Table

[Ashish_Mathur]

Hi,

The calculations will become a lot easier if, from the Year and term, we can generate a date (could be the first date of the term).  So please let me know the starting date of each of the four terms for any year.

[BlueTeam]

Yes, I use another lookup table for those data.  Here they are by YearTerm, which is in the first shared table.

YearTerm	StartDate
20161	6/22/2015
20162	9/28/2015
20163	1/4/2016
20164	3/28/2016
20171	6/20/2016
20172	9/26/2016
20173	1/9/2017
20174	4/3/2018
20181	6/26/2017
20182	9/25/2017
20183	1/8/2018
20184	4/2/2018
20191	6/25/2018
20192	9/24/2018
20193	1/7/2019
20194	4/1/2019
20201	6/24/2019
20202	9/23/2019
20203	1/6/2020
20204	3/30/2020

[Ashish_Mathur]

Hi,

Please also share the start dates for 20151, 20152, 20153 and 20154 since these Year/term combinations appear in your source data.

[BlueTeam]

Ashish,

Your work on this is so greatly appreciated!  Any chance you can load this new dataset (link below)?  I found flaws in the random generation code for the original sample dataset.

I've verified these data and outcomes against the samples provided below.  Samples include retention over three terms as well as several scenarios of enrollment status change between terms.

Thank you again so very, very much!

Sample Data 2

Unduplidated Headcount by YearTerm, Fall 2015 (20152):

Academic Year	Academic Term	YearTerm	Unduplicated Headcount	Enrollment Status
2015	2	20152	884	Full-Time
2015	2	20152	1412	Non-Credit
2015	2	20152	968	Part-Time

Retention Term-to-Term by Same Enrollment Status Examples:

Full-Time Retention, Fall to Winter to Spring, 2015:

Academic Year	Academic Term	YearTerm	Fall Unduplicated	Winter Unduplicated	Spring Unduplicated
2015	2	20152	884	636	490

Part-Time Retention, Fall to Winter to Spring, 2015:

Academic Year	Academic Term	YearTerm	Fall Unduplicated	Winter Unduplicated	Spring Unduplicated
2015	2	20152	968	476	290

Non-Credit Retention, Fall to Winter to Spring, 2015:

Academic Year	Academic Term	YearTerm	Fall Unduplicated	Winter Unduplicated	Spring Unduplicated
2015	2	20152	1412	533	357

Enrollment Status Change Examples:

Fall Full-Time to Winter and Spring Part-Time Enrollment Status Change, 2015:

Academic Year	Academic Term	YearTerm	Fall Unduplicated	Winter Unduplicated	Spring Unduplicated
2015	2	20152	884	100	45

Fall Non-Credit to Winter and Spring Full-Time Enrollment Status Change, 2015:

Academic Year	Academic Term	YearTerm	Fall Unduplicated	Winter Unduplicated	Spring Unduplicated
2015	2	20152	1412	48	29

Fall Non-Credit to Winter and Spring Part-Time Enrollment Status Change, 2015:

Academic Year	Academic Term	YearTerm	Fall Unduplicated	Winter Unduplicated	Spring Unduplicated
2015	2	20152	1412	54	20

[Ashish_Mathur]

Hi,

You are welcome.  My results do not match with yours.  As can be seen in the screenshot below, the number of students who enrolled in the Full time course for the first time in 20152 were 824 (as per you this figure is 884).  If i simply filter your source dataset with the criteira of Full Time and 20152, copy the ID's to another sheet and then remove duplicates, the count of ID's is 884 (the figure that you are reporting).  However, in this list are 60 students who also enrolled in 20151.  If you exclude those students, then result is 824, which i think is what you really want.  I have not even checked the others figures you are reporting because i'd like to clarify this first.

[BlueTeam]

Any term can be the starting term and any prior attendance (before starting term) should not be factored.  So in the above sample results I gave, I used 20152 (fall term of 2015) as the starting cohort.  The retention rates are calculated by only those students in attendance for that specific term and then carried forward (matched) to the following terms and as selected by the end user.  I could have just as easily looked at 20154 (spring term of 2015) and compared this cohort to 20162 (fall term 2016).  The results should always compare the cohort term (starting term) to any following terms selected in the filter, irrespective if the student has attended prior.

Example:  User wishes to identify students from Fall Term 2015 (20152) who maintained a Full-Time enrollment status for Winter and Spring terms of the same year.

Steps to Define Cohort (starting term students) (in sql, this would be an left outer join):

1. User selects starting cohort year/term = "20152"
2. User selects an enrollment status of="Full-Time"

Steps to Define Retention Metrics for Subsequent Terms:

1. User selects all following terms to track retention by enrollment status.  In this example, user would select year/terms "20153" and "20154".
2. User selects one or more enrollment status types.  In this example, user selects only "Full-Time"

Results:

1. Grid showing unduplicated count of starting cohort (year/term=20162) and sees a total of 824 unduplicates students in starting cohort.
2. Subsequent year/terms selections show Winter Term retention of 636 and Spring Term retention of 490.  In grid form, it might look something like this.  These students attended each of the three terms and all maintained an enrollment status of "full-time".

20152	20153	20154
FT	FT	FT
824	636	490

Example 2:  User would like to look at all possible retention metrics for these same students.

Steps to Define Retained Metrics:

1. User selects all enrollment status types for retained students (right side).  Results might look something like this (note that I did not calculate the empty cells yet so some will appear blank.  So in this example, of the 824 Full-Time for the starting cohort, 100 switched to part-time in winter term and only 45 of those remained part-time in spring term.  And, of the 968 part-time in the cohort, 476 remained part-time for winter and of those, 290 remained part-time in spring.

	20152	20152	20152	20153	20153	20153	20154	20154	20154
	FT	PT	NC	FT	PT	NC	FT	PT	NC
FT	824	-	-	636	100		490	45
PT	-	968	-		476			290
NC	-	-	1412	48	54	533	29	20	357

Again, Ashish, I really appreciate your guidance here and I feel bad about using so much of your time.  I think if I could get to the above full metric grid, I might be able to take it from there.  Thanks again!

[Ashish_Mathur]

Hi,

OK.  So this is that i get then.  Of the 884 students enrolled in Full Time in 20152, only 531 continued with the same course in 20154.  However, for you, this figure is 490.  Please recheck.  I have cross checked this figure - it should be 531.  Our figures also differ in range E11:E12 - so please cross check those as well.

[BlueTeam]

Yes, there are 531 matching records between 20152 and 20154 if I am measuring retention for just those two year/terms.  In the example above, I am attempting to sequentially measure 20152-to-20153-to-20154.  This means that the students must have attended each of those terms and have the same enrollment status each term.  Below is an explanation.

These are the unduplicated headcount for "Full-Time for each of the following terms.

20152=884

20153=1052

20154=1157

20152 is the starting cohort and if we join this dataset to 20153, you will see that there are 636 matching records.  I used a left-outer join on ID.  If I then use another left-outer join to the next term in the metric (20154), there are only 490 students.  These 490 students attended each of the aforementioned terms with an enrollment status of "Full-Time".

To your point, had the user selected only 20152 and 20154, the result would have been 531 matching students, just as you have noted.  But because we added another term that sequentially falls between those two terms, the matching total drops because some students either did not attend or dropped below the full-time status.

Hope that makes more sense?

[Ashish_Mathur]

Hi,

I am clear now.  I have made multiple attempts but have not been able to solve it.  I will continue trying and if i can solve it, i will share the solution with you.  I'd request you to not start a new thread but just in case you do, please share the link of that thread with me so that i can subscribe to that one and learn from someone else's solution.

[BlueTeam]

Thanks, Ashish!  This is a complex problem but in SQL, the logic is fairly simple.  I'm just not familiar enough with Dax to figure this out - especially as the logic must be dynamic in order to solve multi-term variables.

I may just try to solve this for one year at a time: i.e. 20152-to-20153-to-20154 by building different views in sql and joining in Power BI.

Thanks again for your efforts!

[BlueTeam]

Sorry about that.  These days may not be exact but should be within a day or two.  I don't have access to the historical calendar at the moment but can always update those values later.

YearTerm	StartDate
20151	6/23/2014
20152	9/29/2014
20153	1/5/2015
20154	3/29/2015
20161	6/22/2015
20162	9/28/2015
20163	1/4/2016
20164	3/28/2016
20171	6/20/2016
20172	9/26/2016
20173	1/9/2017
20174	4/3/2018
20181	6/26/2017
20182	9/25/2017
20183	1/8/2018
20184	4/2/2018
20191	6/25/2018
20192	9/24/2018
20193	1/7/2019
20194	4/1/2019
20201	6/24/2019
20202	9/23/2019
20203	1/6/2020
20204	3/30/2020