Rolling Totals with Unique End Dates

Hi j89k,

I hope you are doing well today 😄❤️

So the problem is that you need to count students who were enrolled on each date which means students whose First_Date is on or before the current date AND whose Last_Date is on or after the current date (or null if still enrolled)

Here is some solutions that should work for you:

First Approach : Active Enrollment Count (I recommend you this)

Active_Enrollment_Count =
VAR CurrentDate = SELECTEDVALUE('Calendar'[Calendar_Date])
RETURN
CALCULATE(
    COUNTROWS('Spring2025'),
    FILTER(
        ALL('Spring2025'),
        'Spring2025'[First_Date] <= CurrentDate &&
        (ISBLANK('Spring2025'[Last_Date]) || 'Spring2025'[Last_Date] >= CurrentDate)
    )
)

Second Approach : Using Both Relationships

• If you want to use your existing relationships:

Active_Enrollment_Count_v2 =
VAR CurrentDate = SELECTEDVALUE('Calendar'[Calendar_Date])
RETURN
CALCULATE(
    COUNTROWS('Spring2025'),
    USERELATIONSHIP('Calendar'[Calendar_Date], 'Spring2025'[First_Date]),
    'Spring2025'[First_Date] <= CurrentDate
) - 
CALCULATE(
    COUNTROWS('Spring2025'),
    USERELATIONSHIP('Calendar'[Calendar_Date], 'Spring2025'[Last_Date]),
    'Spring2025'[Last_Date] < CurrentDate
)

Third Approach : Comprehensive with DayMark

• Since you mentioned wanting to use DayMark as your X-axis:

Active_Enrollment_By_DayMark =
VAR CurrentDayMark = SELECTEDVALUE('Calendar'[DayMark_Value]) // You will need to create this in your Calendar table
VAR TermStartDate = DATE(2024, 11, 6) // Or get this from your term table
VAR CurrentDate = TermStartDate + CurrentDayMark
RETURN
CALCULATE(
    COUNTROWS('Spring2025'),
    FILTER(
        ALL('Spring2025'),
        'Spring2025'[First_Date] <= CurrentDate &&
        (ISBLANK('Spring2025'[Last_Date]) || 'Spring2025'[Last_Date] >= CurrentDate)
    )
)

To Fix Your Calendar Table Issue:

• You need to make sure that is your Calendar table has every date in your range, even if no enrollment changes occurred. Create a complete date table:

Calendar = 
VAR StartDate = DATE(2024, 11, 1)  // Adjust based on your needs
VAR EndDate = DATE(2025, 5, 31)    // Adjust based on your needs
RETURN
ADDCOLUMNS(
    CALENDAR(StartDate, EndDate),
    "DayMark_Value", DATEDIFF(DATE(2024, 11, 6), [Date], DAY)  // Relative to term start
)

So why Your Current Approach Does not Work?

• Your RT_First only counts students who enrolled by that date, but does not subtract those who dropped
• Your RT_First2 tries to count students between two dates which is not what you want for active enrollment
• Missing dates occur because your measure only returns values when the filter context includes students

Some Tips For you...

• Create a complete Calendar table with all dates

• Remove active relationships to Spring2025 (or keep them inactive)

• Use the measures above that explicitly handle both First_Date and Last_Date