Forum Discussion

JCTX_2's avatar
JCTX_2
Frequent Visitor
2 years ago
Solved

Help Accounting for Time w/ Overlaps and Gaps

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.

Need Help
  • OwenAuger's avatar
    OwenAuger
    2 years ago

    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

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    2 years ago

    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