Forum Discussion

j89k's avatar
j89k
Regular Visitor
9 months ago
Solved

Rolling Totals with Unique End Dates

Constructing Rolling Totals with Unique End Dates
3 seconds ago

I work for a university and have been tasked with improving an enrollment dashboard.

The previous analyst set up a query with millions of rows that essentially loads a row for each student for each day that they were enrolled. The query takes entirely too long to refresh and taxes the server.

I've created a dataset that contains one row per student. For now I've reduced the data to a single term, but ultimately it will contain one row per term per student.


I'd like to generate a running total that also accounts for students that drop all of their courses. Ultimatley, I'd like to use DayMark as the X-Axis, this represents the day's away from the start of the term. This will also allow me to visualize across terms relatively easily. For testing purposes I've been focused on using the date columns.

I was able to generate a rolling total using this code:

RT_First =
VAR RefDate = SELECTEDVALUE( 'Spring2025'[First_Date] )
VAR DatesToUse =
    Filter(
        ALL( 'Calendar'[Calendar_Date] ),
        'Calendar'[Calendar_Date] <= RefDate
        )
VAR Result = CALCULATE( COUNTROWS('Spring2025'), DatesToUse )

RETURN Result


I've been unable to modify the code to account for my [Last_Date] variable.

I currently have an active 1 to many relationship with my [First_Date] variable. I also have an inactive 1 to many relationship with my [Last_Date] variable.

I spent some time messing around with USERELATIONSHIP, but couldn't ultimatley produce anything useful.

This is the code I've tried that doesn't produce the result I'm looking for:

RT_First2 =
VAR RefDate = SELECTEDVALUE( 'Spring2025'[First_Date] )
VAR RefDate2 = SELECTEDVALUE( 'Spring2025'[Last_Date] )

VAR Result =
    CALCULATE (
        COUNTROWS ( 'Spring2025' ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Calendar_Date] >= RefDate
                && 'Calendar'[Calendar_Date] <= RefDate2) )

RETURN Result

 

 

 

The other issue I'm having, is that I'd like to generate a value for each date, even if there is no movement from the previous date. My code seems to only generate data points for dates during which enrollment changed.

  • Ahmed-Elfeel's avatar
    Ahmed-Elfeel
    9 months ago

    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

    if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

2 Replies

  • Ahmed-Elfeel's avatar
    Ahmed-Elfeel
    Super User
    9 months ago

    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

    if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.
  • Praful_Potphode's avatar
    Praful_Potphode
    Super User
    9 months ago

    Hi j89k ,

    Can you create a column to see if student has dropped using below column for dax.

    Dropped = IF([Last_Date] < [Term_End_Date], 1, 0)

    The use it to filter your countrows measure?

    sample PBIX.

    Thanks and Regards,

    Praful