Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
HappyFeather
New Member

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

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_IDStudent_NameQuit_dateAdmit_termCourse
1Student111-Feb-242401Course A
2Student220-Feb-242401Course A
3Student33-Mar-242401Course A
4Student410-Mar-242401Course A
5Student513-May-242401Course A
6Student614-Jun-242401Course A
7Student715-Mar-242401Course B
8Student812-Apr-242401Course B
9Student97-May-242401Course B
10Student1018-Jun-242401Course B
11Student1120-Jun-242401Course B
12Student1220-Jun-242404Course C
13Student1321-Jun-242404Course C
14Student143-Jul-242404Course C
15Student155-Jul-242404Course D
16Student1630-Aug-242404Course D
17Student177-Sep-242404Course D

Start dates of the admit terms

Admit_termStart Date
24011-Jan-24
24041-Apr-24

 

This is what I need the chart to look like.

 

HappyFeather_0-1736156707610.png

 

 

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.

HappyFeather_1-1736157353912.png

HappyFeather_3-1736157434664.png

My final result

 

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

HappyFeather_4-1736157502016.png

 

 

 

 

1 ACCEPTED SOLUTION
johnt75
Super User
Super User
1 REPLY 1
johnt75
Super User
Super User

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors