Sequential Terms (dates by clients)

[cmcreynolds]

I posted this here because I will be extracting my data using Power Query and I'd like to solve it there or at least in the Power Pivot data model.

(Similar to one of my recent posts, but conceptually harder)

I work with higher education (several different colleges/universities) and I have recently been given a task regarding term retention - more specifically: students completing subsequent terms.

This is where it gets hairy:

• None of my colleges' terms line up/start on the same date.
• A student can start in any term.
• A student can can take as many courses as they wish, which means on student can take 6 terms to complete their degree while another could complete in just 4 (theoretically).
• I'm looking at historical data, so, of course, start dates are different each year.

I have a separate table that lists institution and term start date. I thought it might help, so I have also created a field in that table that numbers the terms in order: SchoolA has terms numbered 1, 2, 3, 4... and SchoolB has terms numbered 1, 2, 3, 4... (I always look up the student in the correct school, first, in my Lookupvalues and I won't compare schools.)

The big question is:
I need to know the completion rate from students going from their first term to their second term; then their second term to their third term; so on until the sixth term to their seventh term.

I've had the idea of "dummy coding" fields 1-2nd, 2-3rd, etc. and just giving them 1 or 0, but I don't know how to do that in Power Query (or Power Pivot).

@Imke - similar to my previous post?

Thanks for your help.

 

[ImkeF]

A view of your tables and a formula how want the completion rate to be calculated would be very helpful here

 

[cmcreynolds]

Client	StartDate	Sequence
AHI	8/1/2014	1
AHI	10/2/2014	2
AHI	1/7/2015	3
AHI	3/4/2015	4
AHI	8/2/2015	5
CPT	9/3/2013	1
CPT	11/1/2013	2
CPT	2/15/2014	3
CPT	6/6/2014	4
PLZ	8/6/2013	1
PLZ	1/8/2014	2
PLZ	8/7/2014	3
PLZ	1/9/2015	4
PLZ	8/8/2015	5

<tbody>
</tbody>

This is the dates table. Like I said, I just added the sequencing thinking it'd be useful, but I can't think of how.

Here is the main table with student data:

Client	StudentID	Course	StartDate	Sequence
AHI	A1009	ENG101	8/1/2014	1
AHI	A1009	PSY210	8/1/2014	1
AHI	A1009	BIO105	10/2/2014	2
AHI	A1009	MUS310	3/4/2015	4
AHI	A1009	LIB295	8/2/2015	5
CPT	NN906	NUR202	9/3/2013	1
CPT	NN906	NUR315	2/15/2014	3
CPT	NN906	BIO400	6/6/2014	4
PLZ	7000001	MBA202	1/8/2014	2
PLZ	7000001	MBA400	8/7/2014	3
PLZ	7000001	MBA500	1/9/2015	4
PLZ	7000001	MBA600	8/8/2015	5

<tbody>
</tbody>

Trying to explain the next part is where it gets confusing. So, let me get clarification from my "higher ups" and continue this thread.

Thank you.