Efficient Code

[maddogp]

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 

[daxer-almighty · Accepted solution]

@maddogp 

Please supply good examples of your live data. The two first 'tables' are not very informative. Also, please show us the model. DAX can't be written in a vacuum.