Help Accounting for Time w/ Overlaps and Gaps

[JCTX_2]

Hello all,

I am still pretty new and I have a challenge I have not been able to overcome. My 'enrollments' table looks like this:

I am looking for a measure (or any other option) that calculates the total number of months each unique student_id was considered "active" for.

For student_id 101, it should be 1 since they were only active in January of 2000.

For student_id 202, it should be 4 since they were active in January and February of 2000, plus January and February of 2001. I can't use the MIN and MAX functions because that would count all months between January 2000 and February 2001, which would be wrong. The student was not active between March 2000 - December 2000.

For student_id 303, it should be 15. The student was active consecutively from January 2000 to January 2001, plus April and May of 2001. Again I can't use the MIN and MAX functions here due to the gap between enrollments, and I also don't want to "double-count" from March 2000 to December 2000 which are accounted for in both enrollment_id's 4 and 5.

Any help would be greatly appreciated.

[OwenAuger · Accepted solution]

Hi @JCTX_2 

Here's what I would recommend (PBIX attached):

1. You must have a Date table that includes at least a Date column and a month/year column such as "Start of Month". The Date table must include a range of dates spanning all start_month & drop_month values.
2. This Date table does not need to be related to your enrollment table.
3. In the enrollment table, start_month & drop_month should be stored as dates following some convention, such as start-of-month dates.
4. Then create a measure like this:

Active Student-Months =
VAR EnrollmentMonths =
    GENERATE (
        enrollment,
        CALCULATETABLE (
            VALUES ( 'Date'[Start of Month] ),
            DATESBETWEEN (
                'Date'[Date],
                enrollment[start_month],
                enrollment[drop_month]
            )
        )
    )
VAR StudentMonths =
    GROUPBY (
        EnrollmentMonths,
        enrollment[student_id],
        'Date'[Start of Month]
    )
RETURN
    COUNTROWS ( StudentMonths )

1. This measure first uses GENERATE to iterate over the rows of enrollment (EnrollmentMonths variable).
   1. For each row of enrollment, it joins a table of values of Start of Month spanning start_month to drop_month.
2. This table is then grouped by student_id and Start of Month using GROUPBY. This gives us a table containing one row per student-month (StudentMonths variable).
3. Finally, it returns the row count of StudentMonths.

This article may be of interest on a related topic:

https://www.daxpatterns.com/events-in-progress/

Regards

[JCTX_2]

Thank you @OwenAuger. This worked exactly as expected!