Cohort Analysis - Higher Education Style

So I will start off by first saying I watched the Enterprise DNA videos on Cohort Analysis and then Grouping and Segmenting. I think perhaps my specific scenario is not as straight forward so I couldn’t follow those videos to arrive at the outcome that I need for my specific case.

What am I trying to Accomplish?
For the students who have graduated, place them in a cohort that is based on their Admitted Academic Year.

  1. The first thing I know that needs to be done is to get the time to completion in Years which would be using the Admitted Date and the Degree Conferred Date because that will tell how many years it took them to complete the degree.

  2. The next thing I know that needs to be done is we need to ensure that the individuals are tagged to their cohort and that it respects the Academic Career. So if the Admitted Academic Career is not equal to the Graduation Career they should not be excluded from the cohort grouping. But I do not know how to accomplish this and everything after this point. I attached the PBIX file as well.

GraduationCohort.xlsx (17.7 KB)
CohortGroupings.xlsx (9.0 KB)
Graduation Cohort Analysis.pbix (320.0 KB)

Below I tried to create a visual of the final table and how I figured the things that lead to the final table should be generated and where they are placed. I have attached excel spreadsheets if that helps.

To me this sounds very similar to the example that was covered throughout the session.

Exactly the same techniques should be used in the model and with the formula structure. You would just need to adjust the parameters in the appropriate places for your scenario.

My big recommendation here is look to simplify this first. I think you’ve gone all out here and trying to work out the full formula, instead of building it up slowly.

I would do things like only looking at a subset of these first and then including the others.


Then I would look at your model.

How are you attempting to ‘tag’ each person in a specific cohort? Does it look similar to the techniques I used in the example workshop?

To me your model isn’t setup correctly to do this. You likely need a lookup table with will filter based on the cohort.

Similar to the below setup.

It’s just doesn’t look like you have the setup correctly here that enable you to calculate what you need, so really have a good think about this more.


In a way I figured I was tackling too many things Associates, Bachelors, and Masters at one time before figuring out one. I was thinking that I could tag the individuals to a cohort using Switch True(). So if the degree is associates and the individuals time to completion is greater than 0 but less than 2 then I should call them “Associates - Standard Time.”

I need to watch the video again because there are a lot of things and I think because my example has extra factors that I am overthinking it but also not seeing how to morph the Time-Based Cohort Analysis properly.

What if my students have two entries that I need to ensure I keep seperate. They have an Undergraduate Studies Entry and a Graduate Studies Entry. Would I create a table that has both dates along with Undergraduate and Graduate so that I know which is which?

I am going to walk it back by focusing only on Associates tomorrow and update on my progress.

I really recommend here simplifying this.

Even with your explanation I’m confused as to what you need.

Break it down into simple parts and build it out bit by bit using the knowledge you already have at easier steps.


I was able to accomplish what I needed. I was actually thinking about it backwards. I was using the Retention formulas when I should have been using the Churn formulas to show the exit point for the student. When I switch the formulas that I was using in the visuals and broke each degree level into separate formulas Associates, Bachelors, and Masters the outcome was as expected.

I really wanted to see when they graduated (churn). It also probably handled the anomalies that we see in our data where they started one level but migrated to another. The formula worked perfectly for those outlier cases as well.

As expected it counts the one student who was admitted to associates and graduate from associates taking 521 days under “Associates - Standard Time.” Since the other student was admitted to associates but then graduate from a bachelors hes not shown with a cohort because he does not meet the criteria to display a cohort group (period).

Ok great