Every report, track or reward, exam grade or attendance result has a date field. These are input in dd/mm/yyyy format. However, in this format it is only possible to filter by specific date, rather than by academic year, term name, month or week number. Being able to apply a filter that shows all relevant data for an academic year, term or particular month is incredibly useful.
PowerBI enables a calendar table to be quickly and easily created in Power Query. From a date field in the format dd/mm/yyyy, Power Query enables you to create a column for the year, month, week number and so on. Unfortunately though, it can’t easily create date fields that are relevant to education.
For example, the date 15/09/2019 needs at least an academic year (2019-20) and term name (Michaelmas Term).
Creating these in Power Query requires the use of conditional columns. If the month number is between 8 and 12 then the term is Michaelmas, if the month number is between 1 and 3 then the term is Lent, and between 4 and 7 the term name is Summer.
The academic year is a little more involved. If month number is between 8 and 12 then academic year is [year] – [year+1]. So with the date 15/09/2019, the month is 9 (between 8 and 12) and therefore the academic year is 2019 – 2020 (year and year+1).
If the month number is between 1 and 7, then the academic year is [year-1] – [year]. With a date of 10/01/2020, the month is 1 (between 1 and 7) therefore the academic year is 2019 – 2020 (year-1 and year).
You could add these conditional date columns and other date fields for every date in every table that has a date field. I would avoid this for two main reasons:
- It is very inefficient and will increase the size of each table, potentially making the performance slower
- You will have many disconnected date fields in different tables resulting in a poor user experience.
Imagine you have a single page that includes report and attendance data. The report and attendance tables have date information which includes academic year as explained above. If we filter by the academic year in the report data table we will only filter that table, but not the attendance data table. If we also want to filter the attendance data, we have to use another filter to do so. The results in a poor user experience and possible errors – will users know they have to filter the date multiple times per page?
A common calendar table is the answer
To make it, first of all add the date field to the link table and link table key:
- Duplicate the date column in every fact table with a date field and change the data type to text
- Create the link table key including the text version of the date column
- Reference the table, rename it to tablex_LinkTable, select the relevant columns you need to keep for the link table including the date field then append this table to your link table
Create the calendar table:
- Create a new table called Common Calendar Table
- Create a new column that includes dates in dd/mm/yyyy format between a specified range (I have used a parameter to do this)
- Create the other columns based off the date e.g. year, month number, month name.
- Then create the conditional date columns e.g. academic year and term name
Add the relationship between the calendar table and the link table:
- Now create a relationship between the date in the Common Calendar Table and the date field in the Link Table
Your link table is then connected to all the other fact tables (report, tracks and rewards, attendance data etc.) and because the link table contains the date field you can now filter dates from the Common Date Table. This will allow multiple tables to be filtered from a single field. For example, if a report page has report, attendance and tracks and rewards data, a filter from the Common Calendar table has the ability to filter both the report and attendance tables based upon any of the fields in the calendar table.
Creating a common calendar table is an important step to enable data to be filtered by time periods that are relevant within an educational context. Power Query makes it easy to do and then you just need to connect it to your link table.