Working in school environments we often encounter time perios that are completely unique, terms and academic years spring to mind, and each of these needs a differnt approach to the more commonly used DAX formulae. Another example of this is the concept of an academic week. Academic weeks usually start with the first week in September, with week 52 falling in the final week on August.
So if you want to add an academic week column to your date table, here’s one potential way of doing it. It’s not perfect, but it’s quick and simple to create.
First, you’ll need a date table. Explaining date tables is beyond the scope of this article. I have a range of online courses that goes into the creation of date tables here: https://courses.schoolanalytics.co.uk
Once you have your date table, add a standard calendar week calculated column using the built in DAX function:Calendar Week Number =
WEEKNUM ( ‘Attendance Dates'[Date], 2 )
Finally, add this calculated column:Acacdemic Week Number =
VAR CalWeekMinus35 = ‘Attendance Dates'[Calendar Week Number] – 35
VAR AcademicWeek =
‘Attendance Dates'[Calendar Week Number] >= 36,
CalWeekMinus35 + 52
This formula works for the majority of schools, but notice that it can’t account for school holidays, so if your school’s academic weeks don’t include school holidays (and hence there are less than 52 weeks in your school year) you will need a differnt solution.