Analyzing Multiple Date Tables in Power BI

Total: 2 Average: 5

One of the common cases is analyzing multiple Date tables in a Power BI report. It is done with the Power BI Desktop app once the report data model is ready. This article deals with this case, exploring a special use case scenario of data reporting in a data warehouse business intelligence solution.

CodingSight - Multiple Date Tables in Power BI

Multiple Date Tables Scenario

You may want to use multiple Date tables for analysis and reporting for many reasons, including the following:

  1. A single date dimension is not enough to analyze all your data.
  2. The business users are equally interested in different date columns of a table or tables.
  3. The data analyses and reporting by two different customers require different date tables.
  4. The reporting solution naturally depends on more than one date table to give a complete view of the figures and calculations.

When your Power BI reporting solution is backed up by a functional data warehouse BI solution (database/data model), you can accomplish this goal. Let’s see, how.

Prerequisites 

This article assumes the following:

  1. You have the Power BI Desktop installed
  2. You have a good understanding of relational databases.
  3. You can comfortably write and run T-SQL scripts against SQL databases
  4. You are familiar with the basics of data warehouse
  5. You have set up a sample data warehouse database and entered data into the Power BI report tables
  6. There are two date tables (Reg Date and Exam Date) joined with a FACT table through the date columns in the data model of the Power BI report, as described in the reference article

Reference Article

There is a reference article which you should read and implement the steps described in it to fulfill the above requirements. We’d recommend referring to that article to refresh your knowledge.

How and When to use Multiple Date Tables in Power BI

After fulfilling the steps mentioned there, you should have a Power BI report with the following tables:

  1. Reg Date
  2. Exam Date
  3. FACT Exam

The Model view should be as follows:

Basic BI Report

Analyzing Multiple Date Tables

Now we can perform the three main types of analysis of the above-mentioned multiple tables scenario:

  1. Analyze Exam data by registration date (Reg Date)
  2. Analyze Exam data by examination date (Exam Date)
  3. Analyzing Exam data by both registration date and examination date

Analyze Exam Data by Registration Date (Reg Date)

Since this is not a real-time analysis, we’ll create a Power BI report (or a page in it) with at least one visual to view the exam data by registration date.

Rename the Power BI report by saving it as Analysing Multiple Date Tables Exam Report:

Analyzing Exam data by registration date (Reg Date)

Switch to the Report view and right-click on Page 1 at the bottom. Click Rename and enter Exam Data by Reg Date:

Rename table in Power BI

Drag the Matrix visual from the Visualizations pane onto the Power BI designer surface:

Drag the Matrix visual from the Visualizations pane onto the Power BI designer surface

Select the following fields of the FACT Exam table from the Fields pane and click the Focus mode at the bottom:

  1. Total Appeared
  2. Total Fail
  3. Total Pass
Click the focus mode

We can see overall figures related to the Exam in the Focus mode:

Figures related to the Exam in the Focus mode

Expand the Reg Date table and select the Year Quarter Month hierarchy:

Select the Year Quarter Month hierarchy

As soon as you bring this hierarchy into the visual, you will notice a small plus sign next to the year (The year 2021). Thus, you can drill the data from years to quarters and from quarters to months. That is one of the benefits of using the Date table.

Click the plus sign to drill the data down from year to quarter. Here you can see in what quarter of the year the registrations took place, and what were the results (passed or failed).

Drilling data down from year to quarters

Let us click plus sign next to Quarter 1 and Quarter 2 to expand the data. We can see the months when students got registered, appeared in the exam, passed, or failed:

drilling down data from quarters to months

We could infer other facts from this analysis of the visual after drilling down from year to month. For example:

  1. It seems that more and more students are getting registered over time, but we cannot be sure of that until we have the whole year’s figures.
  2. There were no registrations in February 2021 which may be a subject for further investigations whether the organization was not prepared to register new students or there were no available seats.
  3. March has fewer registrations as compared to January and April, so it is also worth investigating.

Now we can understand how the Date table with the Year Quarter Month hierarchy is helping us to analyze the available data based on registration dates.

Analyze the Exam Data by Examination Date (Exam Date)

Right-click on the report page title Exam Data by Reg Date and click Duplicate Page:

Analyze the Exam data by examination date

Rename this page to Exam Data by Exam Date:

Rename the page

Now uncheck the Year Quarter Month hierarchy in Reg Date and check the Year Quarter Month hierarchy in the Exam Date table once we are on the Exam Data by Exam Date page:

Analyse data by examination date

Similarly, switch to the Focus mode of the Matrix visual:

Swith to the focus mode

Expand the Year 2021 to view the quarterly exam data based on exam dates:

Expand the Year 2021 to view the quarterly exam data based on exam dates

Next, expand Quarter 1 and Quarter 2 to drill down to months:

Expand Quarter 1 and Quarter 2 to drill down to months

Now we can extract different types of data:

  1. Examinations were only held twice until now. Is this a normal practice or the organisation is short of resources to conduct more exams?
  2. There seems to be one examination in each quarter. Again, is this according to the schedule, or were there any constraints that allowed the organization to conduct only one exam in each quarter?
  3. More students appeared in the exams held in April 2021 as compared to February 2021. This is also worth investigating.

Analyzing Exam Data by Both Registration Date and Examination Date

This bit is tricky because we have used dummy data. Therefore, a true logical explanation of the figures is not possible. However, it is worth experimenting with.

Create a new page in the Power BI report and name it Exam Date vs Reg Date.

Next, drag two Matrix visuals side by side onto the designer surface. Link one with Reg Date and another with Exam Date against the Total Appeared field of the Exam Fact table.

Analyzing Exam Data by Both Registration Date and Examination Date

Remember that the Total Appeared field tells us the total number of candidates who appeared in the examination. Thus, it is somewhat more related to the Exam Date. But there is still a lot that we can learn from the comparative analysis in the Power BI report.

We may infer from the above comparison:

  1. The students registered in January 2021 had their examination in February 2021.
  2. Some registrations took place in March 2021 and April 2021.
  3. Thus, the April 2021 examination either included both groups of students registered in March and April, or just one – those who registered in March.

Congratulations! You have successfully learned to analyze data with multiple Date tables in the Power BI report.

Things to Do

Try the following things to improve your skills further:

  1. Copy the report as Analysing Exam Report Test and create multiple Date tables Published Date and Review Date for the sample database followed by creating Matrix visuals for analysis. Refer to the article Implementing Full-Text Search in SQL Server 2016 for beginners
  2. Copy the report as Analysing Exam Report Test 2 and explore a similar scenario of creating multiple Date tables for the sample database. You should focus on the Student table mentioned in the article Creating and Deploying Multiple Versions of Database through Schema Snapshots followed by creating Matrix visuals for analysis

Haroon Ashraf

Haroon Ashraf

Haroon's deep interest in logic and reasoning at an early age of his academic career paved his path to become a data professional. He began his professional life as a computer programmer more than 10 years ago working on his first data venture to migrate and rewrite a public sector database driven examination system from IBM AS400 (DB2) to SQL Server 2000 using VB 6.0 and Classic ASP along with developing reports and archiving many years of data. His work and interest revolves around Database-Centric Architectures and his expertise include database and reports design, development, testing, implementation and migration along with Database Life Cycle Management (DLM).