Hi folks,
I'm working in SQL Analysis Services, Live Connection.
My goal is to calculate the enrolment in a course, at various times through the semester. For example, at 1wk after start of course, midpoint of the course, and end of the course. However, the start and end dates change each year.
The data is organized as follows:
| 'StudentTimeTable' | | | |
| [StudentID] | [SchoolYear] | [StartDate] | [EndDate] |
| | | | |
As well, in a separate table in the model (but not incorporated in my measure below):
| 'SchoolSchedule' | | | |
| [SchoolYear] | [Semester] | [SemesterStartDate] | [SemesterEndDate] |
| | | | |
I have created this measure for 1wk after the start of the course which works (for past three years and involves manually coding the 1wk date), but I'm sure there is a better way to do this using variables and incorporating the SchoolSchedule table.
Enrolment@1wk = calculate(
COUNTA(StudentTimeTable[StudentID]),
FILTER(StudentTimeTable,StudentTimeTable[SchoolYear] = "20182019" &&
DATEVALUE(StudentTimeTable[StartDate]) <= date(2019, 2, 11) &&
DATEVALUE(StudentTimeTable[EndDate]) > date(2019,2,11) ||
StudentTimeTable[SchoolYear] = "20192020" &&
DATEVALUE(StudentTimeTable[StartDate]) <= date(2020, 2, 10) &&
DATEVALUE(StudentTimeTable[EndDate]) > date(2020,2,10) ||
StudentTimeTable[SchoolYear] = "20202021" &&
DATEVALUE(StudentTimeTable[StartDate]) <= date(2021, 2, 9) &&
DATEVALUE(StudentTimeTable[EndDate]) > date(2021,2,9))
)
I've also create a measure to determine the date 1 wk after SemesterStartDate:
7daysPostStart = CALCULATE(
DATEVALUE(
SELECTEDVALUE(SchoolSchedule[SemesterStartDate]) + 7))
Here's the resulting table (not all courses run each year)
| Course | Year | Semester Start Date | Enrolment@1wk |
| ABC | 20182019 | 2/4/2019 | 22 |
| ABC | 20192020 | 2/3/2020 | 27 |
| DEF | 20182019 | 2/4/2019 | 24 |
| DEF | 20202021 | 2/2/2021 | 18 |
| XYZ | 20182019 | 2/4/2019 | 12 |
| XYZ | 20192020 | 2/3/2020 | 25 |
| XYZ | 20202021 | 2/2/2021 | 4 |
I'm still learning DAX and was hoping someone with more experience could show a better way to write the measure that makes use of the SchoolSchedule table, and allows for flexibility to work with all years and all SemesterStartDate and SemesterEndDates.
Thanks
