Using DAX in Power BI to calculate academic terms and half terms

Academic terms (certainly for UK schools) are difficult to pin down in statistical terms. Typically Easter falls in a different week each year, altering the relative lenths of the spring and summer terms and making direct comparisons between years difficult. The problem is compounded for half terms, and extended into four different time periods (Spring 1, Spring 2, Summer 1 and Summer 2). So most schools employ some kind of standardisation to allow comparisons made between years to be valid.

In Power BI we typically need to use DAX to create academic date tables for each type of date (eg attendance mark date, behaviour incident date etc) we want to analyse. I’ve posted blog articles about date tables before, and we cover date tables in detail on the Power BI Further Skills course. I was recently asked how to create a column for a school that wants to analyse attendance marks by half term which got me thinking about the problems with academic terms, and a possible solution that uses the SWITCH command.

The DAX code required to create a column based on terms is (relatively) easy:

Term = 
    IF(MONTH('Attendance Dates'[Date]) <= 4,
        "Spring",
        IF(MONTH('Attendance Dates'[Date]) <= 8, "Summer", "Autumn")
    )

But all those IF statements will get complicated if we want to calculate the half terms as well, so I recommend using the SWITCH command like this:

Half Term by Month = 
SWITCH(TRUE(),
    MONTH('Attendance Dates'[Date]) >= 11, "Autumn 2",
    MONTH('Attendance Dates'[Date]) >= 9, "Autumn 1",
    MONTH('Attendance Dates'[Date]) >= 6, "Summer 2",
    MONTH('Attendance Dates'[Date]) >= 4, "Summer 1",
    MONTH('Attendance Dates'[Date]) >= 3, "Spring 2",
    MONTH('Attendance Dates'[Date]) >= 1, "Spring 1"
)

This allows us to decide the term based on the month, but if we want to set the half term based on the week number (to give us closer control) we can also use the WEEKNUM function:

Half Term by Weeknum = 
SWITCH(TRUE(),
    WEEKNUM('Attendance Dates'[Date]) >= 44, "Autumn 2",
    WEEKNUM('Attendance Dates'[Date]) >= 36, "Autumn 1",
    WEEKNUM('Attendance Dates'[Date]) >= 22, "Summer 2",
    WEEKNUM('Attendance Dates'[Date]) >= 14, "Summer 1",
    WEEKNUM('Attendance Dates'[Date]) >= 9, "Spring 2",
    WEEKNUM('Attendance Dates'[Date]) >= 1, "Spring 1"
)

When you use SWITCH like this just remember to order the numbers from highest to lowest, as the SWITCH command will stop evaluating the lines as soon as the first condition is met.