Learn to Use Time Intelligence in Power BI

Time intelligence is a smart way of analyzing time period-based data, such as sales according to dates, using built-in time intelligence functions of Power BI. It is one of the most convenient ways because it eliminates the need to spend time on developing specialized solutions for your team.  

The current article will focus on applying Time Intelligence in Power BI reports and dashboards. Additionally, it will share some professional tips.

CodingSight - Learn to Use Time Intelligence in Power BI

What is Time Intelligence in Power BI?

Time intelligence in Power BI depends on at least one Date table (also known as the Date dimension from the data warehouse perspective) that is connected with other tables (mostly the central FACT table) through the date-time columns.

With a connected and marked Date Table, we can drill down and up in different time frames. For instance, we can view the sales data for a complete year and then drill down to that year’s months, weeks of each month, etc.

Another method is making use of built-in time intelligence functions in DAX language (serves to create calculated tables, columns, measures, etc. in Power BI) to see data from multiple perspectives in different time frames.

You don’t have to be a DAX language pro to use the Power BI Time Intelligence features (functions). However, some basic understanding is required.

Time intelligence helps business users to see the data trends that can help to understand the strengths and weaknesses from many points of view, including Sales.

For example, you can instantly view year-to-date sales and understand how your business is running from the beginning of the year up till now. Plus, many other functions are helping to evaluate business trends and define the best course of action promptly.

The key behind utilizing Time Intelligence in Power BI in the best way is almost always a solid, stable, and standard data model design. The required tables must be picked up, enhanced, and linked with the FACT table (a special type of table used in a data warehouse) to work with Date tables.

Using Time Intelligence in Power BI

Let us get some hands-on experience of preparing the environment to use Time Intelligence functions/features in Power BI. This article assumes that you have the Power BI Desktop installed and possess some basic understanding of its work. You can refer to the below articles for the necessary information:

  1. How to Create the Date Table in Power BI
  2. How and When to use Multiple Date Tables in Power BI
  3. Analyzing Multiple Date Tables in Power BI

In most cases, Time Intelligence is used to bring the data for the following queries:

  • Total Sales
  • Total Sales per Product
  • This Year Sales
  • Previous Year Sales
  • Last Year Sales
  • Year to Date Sales
  • Last Year – Year to Date Sales

We can answer all these questions with Power BI reports, using Time Intelligence functions.

Understanding the Data Set for IT Sales

The Data set for the Power BI report consists of the following tables:

  • IT Sale table
  • Date table          

One option is to start with the Date table. We assume that you have the Date generic template and you can import it into your Power BI time report straight away to begin your project.

We’d recommend you read the below article – it describes how you can create and save your Date template:

Why you should be using Date Template in Power BI

You can also download the Date template from my Github repository, but it is better to do it yourself to understand the procedure and specificities better.

Understanding the Data Set for IT Sales

Import Power BI Date Template to start the Project

To begin our project, we are importing the Power BI Date Template. Open the Power BI Desktop and go to the File menu > Import:

Import the Power BI Date Template to start the Project

Click the stored template to open it as shown below:

Importing Power BI Date Template

You will see the following screen once the template is loaded:

Power BI Date Table is imported into the Report

Right-click on Date in the Fields panel and select Mark as date Table > Mark as date table:

Mark as Date Table

Save the report as IT Sales Report with Time Intelligence to some location that you can easily access. We’ll need it for future reference:

IT Sales Report with Time Intelligence

Create and Populate the IT Sale Table

We are going to create this table manually by entering some rows into it in the Power BI report. In practice, you’d load this table from the data warehouse database.

On the Home ribbon, click Enter data:

Create and Populate the IT Sale Table

Fill the IT Sale table to add the following columns:

ProductDateRevenue
Laptop01 Jan 20191000
PC Desktop01 Feb 20192000
iPad01 Mar 20193000
Laptop10 Jan 20202000
Accessories10 Feb 20203000
iPad10 May 20203000
Laptop15 Jan 20212000
Accessories15 Feb 20213000
iPad15 Mar 20213000

Copy and paste the above table into the blank table in the Power BI report and rename it to IT Sale. Click Load:

Creating IT Sale table by manual data entry

Link Tables to form relationships

Switch to Model view and join the Date column in the IT Sale table with the Date column in the Date table to create a relationship between these two tables. This relationship qualifies you to use the Time Intelligence functions for your data:

Link Tables to form relationships

Change the tabular relationship between IT Sale and Date

So, there is a one-to-one relationship between the two tables. However, it is highly recommended to change it to one-to-many because there may be multiple sales on the same date. The one-to-one tabular relationship can’t handle this situation.

Right-click on the active link between the two tables and click Properties:

Editing the Tabular Relationship

Change the Cardinality to One to many (1:*) and set the Cross filter direction to Single. Click OK. This will allow us to insert as many Sales as possible on a particular Date.

Setting up one-to-many cardinality between date and IT Sale Table

The Power BI Data Model now looks as follows:

One-to-Many relationship between Date and Sale

Prepare the Total Sales Calculation

One of the first requirements is to be able to define Total Sales in the Power BI report. It is pretty easy since Power BI has already done this for us:

Total Sales Calculation

This is the default (in-built) behavior of the Power BI report – it summarizes a numeric value to be used in visuals.

Right-click on the Revenue field and rename it to Total Sales:

Revenue renamed as total sales

Analyze Total Sales in Power BI Report

We need to make one adjustment before displaying the figure. Click the Total Sales field and change the following:

  • Format should be Currency
  • The data type should be Decimal
Analyze Total Sales in Power BI Report

Select the Table visual from the Visualizations pane:

Blank Table Visual

Expand the IT Sale table and select Total Sales only when the table visual is in focus.

Select the Total Sales field in the Fields pane to see the Total Sales in the Power BI designer surface (Canvas):

Viewing total sales figure

We can see that the overall sales of all the products match the amount of 22,000 dollars.

Analyze Total Sales by Product in Power BI Time Report

Next, we want to see the total sales by-product:

  • Unselect the previously selected Total Sales field.
  • Select Product and Total Sales in the Fields pane. They will be shown in that same Table visual as follows:
total sales by product
  • Select stacked column chart visual to bring it onto the Canvas to view Total Sales by Product:
Total Sales by Product in canvas

It is very easy to discover that iPad sales have gone way up as compared to other items.

Congratulations! You have successfully learned to prepare the Power BI report for Time Intelligence functions along with showing the Total Sales by Product figures.

Please stay in touch as we will use Time Intelligence functions in the next part of this article to understand better the sales of the products sliced by different but custom-made time frames.

Things to do

Please try the following things to improve your skills further:

  1. Try to bring in another table of customers buying these products and see if you can view Total Sales by Customer.
  2. Crate another Power BI time report called SQL Articles Publishing Report by adding the Article table manually based on the sample database SQLDevBlog5 mentioned in the article. Then join the Published column with the Date table and create the one-to-many relationship.
  3. Refer to the article and try creating another Power BI time report based on the Student table of TechnicalTraining sample database. Build the one-to-many relationship between the Date column of the Date table and the RegistrationDate column of the Article table. See if you can find Total Articles.
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).

Leave a Reply

Your email address will not be published. Required fields are marked *