In this article, I’ll show you how Power BI can create drillable graphs based on terms, academic years and the days of the week.
Edit: There’s lots of really useful comments at the bottom of this article and I do recommend you read them.
Whenever we analyse data, for schools or businesses, the best graphs often show a specific trend over a time period. For example, a graph of behaviour incidents by month, or attainment data by term, or attendance percentages by academic year.
One of the problems that most people have after they have been using Power BI for any length of time is that Power Bi has no concept of academic terms or academic years.
So if you want to use Power BI to create graphs that analyse data by term, academic year etc, you’ll need to do some extra work to get Power BI to interpret a given date as Autumn term, or academic year 2020/21. How to do this? This article shows you how and why.
Date Tables – why do we need them?
At the start of this article I mentioned that Power BI has no concept of academic year or term. But, there’s another reason why date tables are a good idea: efficiency.
Consider a pupil table in Power BI. Maybe we want to add a calculated column that converts the date of arrival (DoA) into the academic year. We could add the calculated column to the pupil data table, in which case Power BI will calculate every pupil’s DoA individually. But many pupils will have arrived on the same date (usually in September), so this method can be wasteful of computer resources. For this reason, its usually better to construct a date table.
Date Tables – how to create one using DAX
A date table is a table just like any other. The key difference is that a date table is generated entirely within Power BI using DAX. We create a date table using a simple DAX command. Go to the modelling tab, click the New Table button, and enter this DAX:
Attendance Date Table = CALENDAR(DATE(2009,01,01),TODAY())
This CALENDAR function will create a new table in your data model called Attendance Date Table. The new table has just a single column which lists all the dates from 1st January 2009 through to today’s date. Using this simple starting point we can add extra columns to interpret this date in as many ways as we need.
Using DAX to calculate the academic year
The first extra column we will create will display the academic year in the format 2008/9 or 2017/18. Click the New Column button in the modelling tab and add a new column to the table using this DAX (credit to PowerBIforSchools blog for this code):
Academic Year = // Credit to PowerBiForSchools for this DAX formula VAR ThisYear = YEAR ( 'Attendance Date Table'[Date] ) VAR LastYear = ThisYear - 1 RETURN IF ( MONTH ( 'Attendance Date Table'[Date] ) > 8, //if the month is greater than 8 (August) ThisYear & "/" & ThisYear - 1999, //else if the month is NOT greater than 8 LastYear & "/" & ThisYear - 2000 )
What year is it?
If we just want to extract the year part of the date, there’s a DAX function for that:
Year = YEAR('Attendance Date Table'[Date])
Is it Monday, Tuesday or Wednesday?
We might want to graph dates by the day name. For example we might need to calculate how many absences happen on a Monday. To do that we need to claculate the day number (1 to 7) and then convert it into a day name (where 1 = “Sunday” etc). Add a new column using this code to calculate the day number:
Day number of week = WEEKDAY('Attendance Date Table'[Date])
Next, convert this number to the actual name using the SWITCH function:
Day Name = SWITCH ( 'Attendance Date Table'[Day number of week], 1, "Sunday", 2, "Monday", 3, "Tuesday", 4, "Wednesday", 5, "Thursday", 6, "Friday", 7, "Saturday" )
Tip: Look in the comments below for alternatives to using the switch command.
What month is it?
Just like we used the day number to generate the day name, we can use the month number to generate the month name:
Month Number = MONTH('Attendance Date Table'[Date])
Next,we can use the SWITCH command again, this time to generate the month name:
Month Name = SWITCH ( 'Attendance Date Table'[Month Number], 1, "January", 2, "February", 3, "March", 4, "April", 5, "May", 6, "June", 7, "July", 8, "August", 9, "September", 10, "October", 11, "November", 12, "December" )
Autumn, spring or summer term?
Here’s another bit of DAX code to convert the date to a school term. In case you’re wonderin why I prefix the terms with a number, it’s to make sure that graphs using the term on the x or y axis display the terms in the correct chronoligical order. There are more elegant ways of doing this, but sometimes this is just easier! Thanks to Ahmed Yaseen for this code.
School Term = //With thanks to Ahmed Yaseen IF ( MONTH ( 'Attendance Date Table'[Date] ) <= 4, "2 Spring", IF ( MONTH ( 'Attendance Date Table'[Date] ) <= 8, "3 Summer", "1 Autumn" ) )
Using the date table in your data model
Now you have a date table, all you need to do is go to the data model and create a relationship between the date in your attendance table (or pupil data table, or assessment table) and the date in the date table. Make sure the relationship is a one-to-many relationship, with the date table on the ‘one’ side of the relationship. You can now use the fields in the date table to slice and group your graphs by term, academic year and day of the week. Here’s what your finished table should look like: