Calculate the number of students enrolled and finished within each month

Question

I am trying to produce a cluster column chart with Month/Year as the x-axis and the number of enrolled and finished students within each month as y-axis, which would look like this (navy: enrolled students, red: finished students; A: April, B: May, etc):

In EXCEL, what I want to do would be simply put as:
Number of Enrolled = COUNTIFS('Student Enrolment Date', '>=01/04/2021', 'Student Enrolment Date', '<=01/04/2021')
Number of Completed = COUNTIFS('Student Finish Date', '>=01/04/2021', 'Student Finish Date', '<=01/04/2021')

Then use the calculated values to produce a visual.

The data example (let's call it Student) is as below:

I have also produced a date dimension as below:

I get confused about how to do this using DAX. If I connect Student['course start date'] with Date['Date'], and use Date['Year-Month'] as x-axis as below, then Power BI will count all students whose Student['course start date'] fall into each month. But because students is not necessarily get enrolled and finished in the same month, this method is apparently incorrect.

My questions are:

I am now trying to count the date in Student['student enrolment date'] and Student['student finish date'] within each month using:

Student Complete = COUNTROWS(FILTER(Student, Student[Status]="Complete" && Student[student enrolment date]>=DATE(2021, 4, 1) && Student[student enrolment date]<=DATE(2021, 4, 30) + 0))

but DAX says this is not a correct formula, and I don't know how to make this formula calculate for each month. Could you please correct me?

In my case, how to properly connect the date dimension to the Student dimension?

Wouter Wouter · Accepted Answer · 2021-07-11T11:05:38

I think your model is not ok and that's why your DAX is much more complicated than necessary.

Create a fact table that connects your dimensions.

e.g. factStudentEvents

StudentId   dateId      Enrollments Completions
----------- ----------- ----------- -----------
1           20210413    1           0
1           20210620    0           1
2           20210427    1           0

Like this your DAX measures are very simple:

Completion Count = SUM(factStudentEvents[Completions])
Enrollment Count = SUM(factStudentEvents[Enrollments])

Calculate the number of students enrolled and finished within each month

1 Answers