How to pivot a count by month showing the total count

[Fair]

First with apologies if this is answered somewhere but Ive gone through a few dozen Q&A and no one seems to approach this use case (surprisingly).

Let’s say we have students and they have an enrollment date and a graduation date.

We want to show the count of the number of students who have enrolled but not yet graduated and pivot that by month to show the current number of actively enrolled students each month.

Pivoting by enrollment date (and filtering for graduation date is null) does not accomplish this because it only gives the number of students that enrolled in that month, rather than showing the total number of students currently actively enrolled during that month.

Say you have 20 students, 14 enrolled in January, 6 in February. Then 4 graduated June 1st. That means in June we have 16 active students (20 enrolled - 4 graduated). We want to show this as count of active students by month, e.g.
January - 14

February - 20

March - 20

April - 20

May - 20

June - 16

July - 16

How can we represent this is a Looker look?

[Fair]

Answering my own question. This was very non-intuitive but figured it out.

Solution:
Create a table in your data source with a column of type date, e.g.:

 create table `months` (`month` date);

Insert as many of these dates as per the frequency you want (e.g. I want by month so I’m inserting 12 dates):

insert into months (month) values ('2021-01-01');

insert into months (month) values ('2021-02-01');

etc…

Back in Looker, add a view for this table, ensure you have a dimension of type time with datatype of date for this new field in your db, e.g.

view: months {
  sql_table_name: `datasource.database.months`
    ;;

  dimension_group: month {
    type: time
    timeframes: [
      raw,
      date,
      week,
      month,
      quarter,
      year
    ]
    convert_tz: no
    datatype: date
    sql: ${TABLE}.month ;;
  }
}
 

Then cross-join this view into your model, e.g.:
  join: months {
    type: cross
    relationship: many_to_one
  }

Now, you can use these months in your explore and looks, and you can pivot based on the month, then you can incorporate a count by creating a custom measure field, e.g.
((extract_months(${student.enroll_date}) <= extract_months(${months.month_date})) AND (is_null(${student.graduation_date})))

And now, finally, we have the result we want of an overall count of active students by month between their enrollment dates and graduation dates