The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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.
Solved! Go to Solution.
SQLBI have a good article and video on this - https://www.sqlbi.com/articles/comparing-cumulative-values-for-events-in-different-periods/
SQLBI have a good article and video on this - https://www.sqlbi.com/articles/comparing-cumulative-values-for-events-in-different-periods/