Create "sum of students" chart based on Start and End Dates of multiple training courses

[Radek_Z]

Hi all,

I've been struggling with this one too long...time to ask the experts:

We run a school with training courses starting and ending at various days of the year. Each course has a different number of students enrolled. There are hundreds of courses, and some are planned up to a decade into the future.

Our challenge is that we need to know how many students we'll have enrolled at ANY DAY in the future, so we can ensure that we have enough instructors and classroom space prepared in advance.

I would like to create a "Sum of Students" line chart that sums the total number of students for ALL courses, based on the Course Start and Course End dates. Here's my sample data:

Course Name	# of Students	Couse Start Date	Course End Date
Course 1	3	15-Apr-2022	03-Mar-2024
Course 2	5	10-Aug-2022	29-Apr-2024
Course 3	2	03-Mar-2023	04-Jan-2025
Course 4	2	10-Oct-2023	25-Aug-2025
Course 5	4	14-Jan-2024	13-May-2026

I would like to create a simple line chart, based on the below sample "expected result" table. I've simulated the X-axis using the above 10 dates (5 start dates and 5 end dates sorted ascending left to right, for demo purposes), and I've simulated the Y-axis as the "Line chart results" row which sums up ALL students that are actively enrolled in ALL the courses on a particular day, IF that day falls within the Course Start and Course End date.

I've mapped the 5 sample courses on the table to illustrate when the students are in training, and how the summing up is expected to happen.

Line chart results	3	8	10	12	16	16	13	8	6	4
Course 1	3	3	3	3	3	3
Course 2		5	5	5	5	5	5
Course 3			2	2	2	2	2	2
Course 4				2	2	2	2	2	2
Course 5					4	4	4	4	4	4
	15-Apr-2022	10-Aug-2022	03-Mar-2023	10-Oct-2023	14-Jan-2024	03-Mar-2024	29-Apr-2024	04-Jan-2025	25-Aug-2025	13-May-2026

I would like the Y-axis to be ONE LINE representing the Sum of Students, and the X-axis to be a continuous date range based on my Date Table, something like this:

Does anyone have an idea for how to accomplish this? Many thanks in advance.

[Radek_Z]

Wow, that did it. Thank you!

I had to watch the video slowly, and took my time replicating your approach with my dataset, but it did exactly what I was looking for.

The sum of Total Active Students measure was more complicated than expected, but it does make sense now that I see the "answer" in front of me. Thank you again, Amit!

[amitchandak · Accepted solution]

@Radek_Z , try if these approaches can help

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM