Culmulative monthly data trend based on duration for courses with different starting dates

[HappyFeather]

I would like to create a chart that shows the trend of students quitting schools based on the duration of the course in months.

There are 2 different starting dates for the courses in a year based on their admit terms.

There should be a trendline for each admit term and all the lines should start from Month 0.

Student Data Sample (edited)

Student_ID	Student_Name	Quit_date	Admit_term	Course
1	Student1	11-Feb-24	2401	Course A
2	Student2	20-Feb-24	2401	Course A
3	Student3	3-Mar-24	2401	Course A
4	Student4	10-Mar-24	2401	Course A
5	Student5	13-May-24	2401	Course A
6	Student6	14-Jun-24	2401	Course A
7	Student7	15-Mar-24	2401	Course B
8	Student8	12-Apr-24	2401	Course B
9	Student9	7-May-24	2401	Course B
10	Student10	18-Jun-24	2401	Course B
11	Student11	20-Jun-24	2401	Course B
12	Student12	20-Jun-24	2404	Course C
13	Student13	21-Jun-24	2404	Course C
14	Student14	3-Jul-24	2404	Course C
15	Student15	5-Jul-24	2404	Course D
16	Student16	30-Aug-24	2404	Course D
17	Student17	7-Sep-24	2404	Course D

Start dates of the admit terms

Admit_term	Start Date
2401	1-Jan-24
2404	1-Apr-24

This is what I need the chart to look like.

The graph should sum up all the cases from start to the end of that month to show up for each month.

Example:

For admit term 2401, "1" month duration = all cases from 1 Jan to the last day of Jan, "2" month duration = all cases from 1 Jan to last day of Feb, and so on.

For admit term 2404, "1" month duration = all cases from 1 Apr to the last day of Apr, "2" month duration = all cases from 1 Apr to last day of May, and so on.

Edited: I manage to create the chart. Documenting down the steps that I took.

Step 1: Go to the table view, added a column in 'Quit List' to put in the "Start date" from 'Admit term Start dates' Link that I learn from (https://www.popautomation.com/post/power-query-merge-tables)

Step 2: Using newly added "Start date" and "Quit_date", I calculate the month difference between them. Created a new column "Which_month_quit" in 'Quit List' and here is my DAX formula.

Which_month_quit = DATEDIFF([Admit term Start dates.Start Date], 'Quit list'[Quit_date], MONTH)+1

Step 3: Go back to the report view. Create new measure on 'Quit List' to sum up the number who quit over the months. Here is my DAX formula.

Sum Group 2 = 
var catagory=LASTNONBLANK('Quit list'[Admit_term],'Quit list'[Admit_term])

RETURN CALCULATE
    (COUNT('Quit list'[Student_ID]),
        filter(
            allselected('Quit list'),
            'Quit list'[Which_month_quit]<=MAX('Quit list'[Which_month_quit])
        ), filter(All('Quit list'),'Quit list'[Admit_term]=catagory)
    )

Step4: Select line chart. Add in the parameters from 'Quit List' in the image.

My final result

Question: How can I extend the line down to zero? Black line is what I need help with.

[johnt75 · Accepted solution]

SQLBI have a good article and video on this - https://www.sqlbi.com/articles/comparing-cumulative-values-for-events-in-different-periods/