I have delivered Power BI training at a number of universities over the last 6 months, and a few of the participants asked me the same question. “What is the best way to add semesters to a calendar so that they can analyse student data by semester?“ There are various ways of solving this problem. In this article I want to share with you one way to solve it using DAX in Power BI. I will use AdventureWorks database for the demonstration. To keep the demo more realistic, I will rename some of the tables (I wont change the data).
- The Customers table will be called Students
- The Sales table will be called Enrolments by subject
- The Territory table will be called Campus
It is not a perfect one of one swap, but it will serve the purpose I think. I wont change the actual data, but use the data as described above instead. I know student enrolment data is different to this, but I don’t have such data available to me – this proxy should illustrate the point of the article well enough.
The final report looks something like this.
As is my normal practice, I will show you how to do it step by step below. The technique I will be using is essentially Banding in DAX that I covered in a previous blog post.
The Sample Data
To start with I have created a master data table with semester start and end dates; I called this table SemesterDates. I have assumed that there will be 2 semesters in a year. Of course some universities will have even 3 or 4 semesters in a year – the principle is the same. I have named the two semesters in a year as S1 and S2. I also added a column YYYYSS to create a unique semester and year column. Lastly I added a Semester ID column to facilitate writing the DAX formulas. Note this last column is an integer that uniquely identifies each semester and increments by 1 for every semester that passes.
This is a simple table that anyone can create in Excel. Once created, this becomes the master table that can be simply maintained each year as the new semester dates are released.
Loading the Semester Dates Table into the Power BI Data Model
I loaded the SemesterDates table into the AdventureWorks Power BI workbook. In this case, I am not going to connect the table with any other table in the data model. The purpose of relationships in Power BI is to propagate filters from one table to another – I don’t need to do this, hence no relationships. I also decided to hide the table so that it will not show up in the Report view. This is because I will use it only to write the required DAX formulas (calculated columns) that are required for reporting purpose. This is my Relationship view of the data model.
Adding Calculated Columns in Calendar Table
I always recommend that when in doubt, add measures and not calculated columns. But there are always exceptions to this rule, as I explained in my blog article Calculated Columns vs Measures in DAX. In this case, we are adding the calculated columns to a Lookup table (Calendar) to enhance it and Lookup tables are normally smaller (less rows) and hence the size impacts of Calculated Columns is not so bad. Also, I want to use the result to slice my data, and a measure cannot do that – I need a column.
I added three calculated columns YYYYSS, Semester and SemesterID using the following DAX formulas shown below. I have used the VAR syntax to make the formulas easy to understand. These formulas are essentially the Banding in DAX technique.
YYYYSS = VAR CurrentDate = 'Calendar'[Date] VAR Result = CALCULATE(SELECTEDVALUE(SemesterDates[YYYYSS]), CurrentDate >=SemesterDates[From Date], CurrentDate <=SemesterDates[To Date] ) RETURN IF(Result = BLANK(), "Vacation", Result)
Semester = VAR CurrentDate = 'Calendar'[Date] VAR Result = CALCULATE(SELECTEDVALUE(SemesterDates[Semester]), CurrentDate >=SemesterDates[From Date], CurrentDate <=SemesterDates[To Date] ) RETURN IF(Result = BLANK(), "Vacation", Result)
Semester ID = VAR CurrentDate = 'Calendar'[Date] VAR Result = CALCULATE(SELECTEDVALUE(SemesterDates[Semester ID]), CurrentDate >=SemesterDates[From Date], CurrentDate <=SemesterDates[To Date] ) RETURN IF(Result = BLANK(), 0, Result)
The trick with the formulas above is to do the following (keeping in mind that it is a calculated column)
- Grab the date for each row in the calendar table (the formula is executed one row at a time – i.e. it has a row context)
- Push a filter onto the semester table so that the semester table shows a single row after the filter is applied.
- The single row must be the row on or after the start date, and also on or before the end date.
- Once the semester table has a single row (after filters have been applied), extract the single value from one of the other columns in the semester table and store it in my new calculated column.
It works because the date ranges in the semester table are mutually exclusive (there are no overlapping date periods where any given date is in 2 semesters).
Defining Measures to Report the Data Insights
Now we are ready to define the required measures to report the data insights. And as the normal practice goes, I have first added a Table Visual to the report with the field ‘Calendar'[YYYYSS] and then keep adding each measure to make sure that my DAX formulas are correct.
The first measure I wrote is to count the number of students. Remember, I am using AdventureWorks here in reality, so it is not a perfect substitute for true enrolments. I am using purchase date from AdventureWorks as a proxy for a transaction date during a semester. The principle is the same, however.
Total Students This Semester = DISTINCTCOUNT(Enrolments[StudentKey])
Total Students Prev. Semester = VAR CurrentSemester = SELECTEDVALUE('Calendar'[Semester ID]) VAR PrevSemester = CurrentSemester - 1 VAR Result = CALCULATE([Total Students This Semester], ALL('Calendar'), 'Calendar'[Semester ID] = PrevSemester) RETURN Result
Chg vs Prev. Semester = VAR CurrentSemester = SELECTEDVALUE('Calendar'[Semester ID]) VAR PrevSemester = CurrentSemester - 1 VAR Result = IF(PrevSemester <> 0, [Total Students This Semester] - [Total Students Prev. Semester] ) RETURN Result
A Final Word
As I mentioned at the start, there is more than 1 way to solve this problem, including using Power Query instead of DAX. The downside of using DAX (this method) is you have to write the calculated columns in every new workbook you create. Regardless, it is a simple and useful way to solve the problem.