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:
We have 6 terms, simply T1-6. However sometimes a month falls over 2 terms (Feb and April this year for example parts of 2 different terms holiday in the middle of the month).
How could I adapt the term DAX to accommodate this please?
I’m not sure Maggie, the formula I’ve used in the example isn’t specific to any particular date – just months. Maybe someone reading this blog can suggest a way forward?
Hi Maggie,
Although this doesn’t exactly answer your question it might help guide you towards a solution. We have a term of birth chart for our group analysis dashboard. In the pupil details dataset I have a column for TermBirth which will output a month number e.g. 1 for jan etc. (see formula below). Then I have a measure for each term and these are based on the term of birth column. You might be able to set up something similar where the measure formula also considers if the date i.e. if before the feb half term then its included in the T3 term etc. I hope this helps a little.
TermBirth = MONTH(‘Group Analysis'[DOB])
Measures for each half term:
Autum = COUNTROWS(filter(‘Group Analysis’,’Group Analysis'[TermBirth]>=9))
Spring = COUNTROWS(filter(‘Group Analysis’,’Group Analysis'[TermBirth]=5 && [TermBirth]<=8))
An interesting post. At first glance it looks over engineered. I would use DayName = FORMAT (Date, “dddd”). Is there a performance benefit by using the functions in the way they’re presented? With over a million rows in our attendance data each year I’m keen to find any short cut!
Great suggestion Ian, thanks!
Complex SWITCH expressions can be avoided:
Month Name = FORMAT ( ‘Attendance Date Table'[Date], “MMMM” )
Month Abbreviation = FORMAT ( ‘Attendance Date Table'[Date], “MMM” )
Day Name = FORMAT ( ‘Attendance Date Table'[Date], “DDDD” )
Day Abbreviation = FORMAT ( ‘Attendance Date Table'[Date], “DDD” )
More great suggestions, Thanks Frank!
Really appreciate this post! I haven’t considered this way of working, so i will take a look at have a go at replicating! thanks
Slightly alternative way of attacking this, which limits the report to a single academic year (as term dates change each year)
I have a separate table that I created in PowerQuery called Lookup_Term – in that table I have term names and start and end dates for each term. Each end date goes up to the day before the new term starts as holidays don’t really need to be considered. This table would need to be updated for each academic year’s report.
I then built a table called Academic Date Table as follows:
Column 1 – define dates:
Academic Date Table = CALENDAR(MIN(Lookup_Term[TermStartDate]), today() )
Column 2 – Academic Year (per David’s post above):
Academic Year =
// Credit to PowerBiForSchools for this DAX formula
VAR ThisYear =
YEAR ( ‘Academic Date Table'[Date] )
VAR LastYear = ThisYear – 1
RETURN
IF (
MONTH ( ‘Academic 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
)
Year column – per David’s post above:
Year = YEAR(‘Academic Date Table'[Date])
Day column (slightly refined following comments above):
Day = ‘Academic Date Table'[Date].[Day]
Month column (slightly refined following comments above):
Month = ‘Academic Date Table'[Date].[Month]
Week Column (running from 1st September – so “tricked” for this to be week 1):
Week = WEEKNUM(‘Academic Date Table'[Date])-35
Term column – looking up the term name from the lookup_term table based on date:
Term =
CALCULATE(
MAX(Lookup_Term[Term]),
FILTER(lookup_term,
Lookup_Term[TermStartDate] = ‘Academic Date Table'[Date])
)
The week column is really useful for graphing attendance or other other data over time using a line chart with the Week No as the axis (tip you can use the Date field as the tooltip which will show you where in the calendar each week falls)
Helen the lookup_term feature will solve your issue I think
Thanks Marian!
Could your formula be adapted to handle multiple academic years and terms?
Yes, it should work as is – create a date hierarchy with academic year at the top, then term, then month, then week and finally day. You can then drop the hierarchy onto the axis of a chart.
Apologies – realised I wasn’t clear that my day column gives a day number, if you want the day name then
Day = format(WEEKDAY(‘Academic Date Table'[Date]),”dddd”)
or for 3 letter day name (Mon, Tue)
Day = format(WEEKDAY(‘Academic Date Table'[Date]),”ddd”)
Really ought to take a break when it is half term, my brain is obviously getting tired !! There is an issue in my original post above with calculating the week number – replace the column from my post above with the following 2 columns:
Week Column (running from 1st September – so “tricked” for this to be week 1), initially need to find out the real calendar week number:
CalWeek = WEEKNUM(‘Academic Date Table'[Date])
Now trick the data to show the Academic Week from September
AcWeek =
VAR __aw = [Calweek] – 36 + 1
RETURN IF(__aw<=0, 52+__aw,__aw)
Going for a lie down now!
Hi, I tried to follow your steps. Very easy to follow!
I seem to be getting errors though when I try and format the day to a word and then also when trying to calculate the CalWeek and AcWeek.
Thank you
Hi Taiyne, what’s the error message?
Hi David,
Great to see you at your Workshop yesterday.
Here’s the DAX to calculate the Years and Months based on DOB:
Years_Months = INT(DATEDIFF(DATEVALUE(PowerBI_Data_Primary[DOB]),TODAY(),MONTH)/12) & ” Years, ” & MOD(DATEDIFF(DATEVALUE(PowerBI_Data_Primary[DOB]),TODAY(),MONTH),12) & ” Months”
I think I might have found a solution for this which deals with terms or half-terms of different lengths.
I stumbled on to this post below which outlines the method
https://exceleratorbi.com.au/adding-semesters-calendar-power-bi/
Briefly:
I made the date table following the instructions that David outlines
I made a second table that had the start/end dates for each half-term.
I made inserted calculated columns using the DAX formula in the exceleratorbi post
I can now create date hierarchy of: academic year / term / half-term / week /day irrespective of the lengths of each term.
After a bit of trial and error with the naming of the calculated columns it all seems to work well.
I think it should be relatively straight forward to make calculation groups (in Tabular editor) so any measure you want can be visualised on a weekly / termly etc basis.
Hope all of this makes sense!!
Ian
Thanks Ian, I will give this a try!