I have taught Power BI training at various universities for the past 6 months and some of the participants asked me the same question. “What is the best way to add semesters to a calendar so that you can analyze student data by semester?“ There are several ways to solve this problem. In this article I want to share with you a way to solve it using DAX in Power BI. I will use the database AdventureWorks for demonstration. To keep the demo more realistic, i will rename some of the tables (I will not change the data).
- The Clients table will be called Students
- The sales table will be called Enrollments by Topic.
- The Territory table will be called Campus
Not a perfect exchange of one, but i think it will serve the purpose. I will not change the actual data, but i will use the data as described above. I know the student enrollment data is different from this, but I don't have that data available; this proxy should illustrate the point of the article well enough.
The final report looks like this.
As is my usual practice, below I will show you how to do it step by step. The technique I will use is essentially Ringed in DAX which I covered in a previous blog post.
Sample data
to get started, I have created a master data table with start and end dates of the semester; I called this table SemesterDates. I have assumed that there will be 2 semesters in a year. Of course, some universities will even have 3 O 4 semesters in a year; The principle is the same. I have named the two semesters of a year as S1 and S2. I also added a YYYYSS column to create a unique semester and year column. Finally, I added a semester ID column to make it easier to write the DAX formulas. Note that this last column is an integer that uniquely identifies each semester and is incremented 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 simply be maintained each year as the new semester dates are posted.
Loading the semester date table into the Power BI data model
I loaded the SemesterDates table into the AdventureWorks Power BI workbook. In this case, I will not connect the table to any other table in the data model. The purpose of relationships in Power BI is to propagate filters a table to another; i don't need to do this, Thus, no relationships. I also decided to hide the table so that it is not displayed in Report view. This is because I will use it only to write the required DAX formulas (calculated columns) required for reporting purposes. This is my relationship view of the data model.
Add calculated columns to calendar table
I always recommend that, in case of doubt, add measures and not calculated columns. But there are always exceptions to this rule, as I explained in my blog article. In this case, we are adding the calculated columns to a lookup table (Calendar) to improve it and The lookup tables they are normally smaller (less rows) Y, Thus, the impact of the size of the calculated columns is not that bad. What's more, I want to use the result to divide my data, and a measure can't do that; i need a column.
I added three calculated columns YYYYSS, Semester Y SemesterID using the following DAX formulas shown below. I have used VAR syntax so the formulas are easy to understand. These formulas are essentially the banding technique in DAX.
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 = WAS 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 above formulas is to do the following (taking into account that it is a calculated column)
- Take the date for each row in the calendar table (the formula runs one row at a time, namely, has a row context)
- Push a filter on the semester table so that the semester table displays a single row after the filter is applied.
- The only row must be the row on or after the start date, and also on or before the end date.
- Once the semester table has only one row (after filters have been applied), extract the unique value from one of the other columns in the semester table and save it to my new calculated column.
It works because the date ranges in the semester table are mutually exclusive (there are no overlapping date periods where a given date is in 2 semesters).
Defining measures to inform data statistics
Now we are ready to define the measures necessary to inform the insights of the data. And as is the usual practice, first I added a Table Visual to the report with the 'Calendar' field[YYYYSS] and then keep adding each measure to make sure my DAX formulas are correct.
The first measurement I wrote is counting the number of students. Remember, i am using AdventureWorks here actually, so not a perfect substitute for actual inscriptions. I am using AdventureWorks purchase date as a proxy for a transaction date during a semester. But nevertheless, The principle is the same.
Total Students This Semester = DISTINCTCOUNT(Enrolments[StudentKey])
Next I define the measure to count the number of students in the previous semester.
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
We can define Chg vs Prior Semester as follows.
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
We finally got the desired report.
Here is the sample workbook I used in this blog post.
A final word
As i mentioned at the beginning, there is more than one way to solve this problem, including the use of Power Query instead of DAX. The downside of using DAX (this method) is that you have to write the calculated columns in every new workbook you create. Regardless, is a simple and useful way to solve the problem.
