Time Intelligence in Power BI: Capitalize on time

Overview

• Understand the importance of Time Intelligence functions in Power BI
• Implement Time Intelligence functions in Power BI with a simple example

Introduction

Time is an asset for businesses just like resources, materials, and money. Businesses use cumulative time-based calculations (Year-to-Date, Month-to-Date, or Quarter-to-Date) that are referred to as YTD, MTD, QTD in common parlance. They are helpful metrics to analyze trends or compare sales and financial performance. Nevertheless, it can be a nightmare to calculate these metrics using pivot tables and formulas in Microsoft Excel.

Though time-related calculations are not complicated, they can be tedious. Here’s a question to business analysts: “How much time do you spend preparing Excel-based dashboards to analyze business performance over time?” The answer is pretty unsatisfactory, isn’t it?

The Time intelligence functions in Power BI helps the users perform calculations that can evaluate business performance on-the-go, without storing them explicitly in a separate sheet and refer them back. Microsoft Power BI offers formula like calculations called Data Analysis Expressions (DAX) that can help the user to come up with new features from the data. Time intelligence functions are go-to expressions for a Power BI developer to measure patterns in business metrics over time (year-on-year, quarter-on-quarter, compared to last year, etc.).

This article discusses the importance and uses of Power BI time intelligence functions that can make the dashboard development process a smooth ride.

Note: We assume you have used Power BI before and have some experience working with DAX. For a beginner-friendly introduction to Power BI, check out the blog:

• 10 Useful Data Analysis Expressions (DAX) Functions for Power BI Beginners

Table of Contents

1. 1. Understanding the data
   2. Importance of Time Intelligence Functions
   3. Working with DAX in Power BI
   4. Creating a Date reference table
   5. Requisite Calculated Columns and Measures
   6. Analyzing business performance with time intelligence functions

Understanding the data

Before diving into the details of how Time intelligence functions in Power BI can be easily and efficiently implemented, we give an overview of the sample data. We use data comprising of Direct Profit numbers for a business for the years 2017 and 2018.

A screenshot of the data has been provided below. The data can also be downloaded for practice from Github.

The data contains Direct Profit numbers for a couple of products. Product descriptions, business segment, brand, and other characteristics have been masked. A couple of campaigns are run each month. For the months of July and December, there is only one campaign. Thus, the data available to us is not in a continuous date format.

Importance of Time Intelligence Functions

To compare trends, businesses rely on evaluating the performance of campaigns over a time window. Generally, business stakeholders want to know about their key performance indicators (KPIs) and compare it to their yearly targets.

For instance, they may want to evaluate the Year-to-Date (YTD) profits and strategize to fill the gap with respect to the yearly target.  In addition, the performance compared to the previous year or month or quarter might be another metric to evaluate. This would help them to analyze whether the business performance matches up with that of the same period last year.

Power BI offers functions that can implement these comparisons efficiently through simple calculations. Though the calculations for these time-based metrics in Excel is not very difficult, they may involve the use of pivot tables.

Working with DAX in Power BI

Let us import the data into Power BI using the “Import Data from Microsoft Excel Workbook” functionality button. The imported data in the Data view of Power BI would be:

Notice that there are no columns that represent the calendar dates with respect to each “Campaign Number” column. Therefore, we will create a calculated column using the “New Column” button under the Table Tools menu. We will use our first Data Analysis Expression (DAX) to arrive at this. DAX is Excel-like formulas that can create new columns in the data.

[stextbox id='grey']CalendarDate = DATE(Data[Year], Data[Month], Data[Date])[/stextbox]

The DATE function in Power BI creates a column of calendar dates using the “Year”, “Month” and “Date” columns from the “Data”. However, note that the column “CalendarDate” that represent dates corresponding to bi-monthly campaigns are not continuous. Though this may not necessarily pose a challenge, an easier way to exploit Time Intelligence functionality in Power BI is to create a new table containing a series of continuous dates.

Creating a Date reference table

To create a new table in the Data Model, we go to the “Home” tab and click on the “New Table” button.

We use a DAX called CALENDAR function that creates a table called “Date” containing a “Date” column given a specific start date and end date that are 01-Jan-2017 and 31-Dec-2018, respectively.

[stextbox id='grey']Date = CALENDAR(DATE(2017,1,1), DATE(2018,12,31))[/stextbox]

Once the “Date” table has been created, we have to mark-out this table as the official Date Table in our Data Model. That can be done by finding the “Date” table in “Fields” pane on the right and click on “Mark as Date Table”. Power BI will ask you to select the column that represents the date. By now you know it is the “Date” column. This step ensures your further DAX formulas refer to this “Date” table as the most reliable source of dates.

Unless we create a relationship between the “Date” table and “Data” table, we can’t use the created table for DAX calculations. To achieve this, we go to the Relationship view of the model and establish a one-to-many relationship between the “Date” column of the “Date” table and the “CalendarDate” column of the “Data” table. The continuous list of dates from the “Date” table makes aggregating time series data much easier.

Requisite Calculated Columns and Measures

To view the time-series as per user convenience, we create new columns in the “Date” table with the following DAX. These columns represent the year, month number, day, month name, quarter.

[stextbox id='grey']Year = YEAR('Date'[Date])[/stextbox]
[stextbox id='grey']MonthNum = MONTH('Date'[Date])[/stextbox]
[stextbox id='grey']Day = DAY('Date'[Date])[/stextbox]
[stextbox id='grey']MonthName = FORMAT('Date'[Date], "MMM")[/stextbox]
[stextbox id='grey']Quarter = "Q" & FORMAT(QUARTER('Date'[Date]), "")[/stextbox]

Having created these calculated columns, the next important aspect is to create our first measure in the “Date” table. The measure for actual direct profit numbers would be created using the below mentioned DAX. Above all, “DirectProfit” would give us the flexibility to capture the numbers from the “Data” table and make it available at any level of detail, be it yearly, quarterly, monthly, or day-wise.

[stextbox id='grey']DirectProfit = SUM(Data[Direct Profit])[/stextbox]

Analyzing business performance with Time Intelligence functions in Power BI

The measure created in the last section can be viewed in the “Report” view of the data model using a table and a chart. This gives us a view of the month over month comparison of Direct Profit.

One of the most widely used business KPI is Year-to-Date (YTD) values. Year-to-Date shows the cumulative performance metrics (Direct Profit in this case) starting from the beginning of the year until the selected date.  We will use the TOTALYTD function in Power BI to calculate the Direct Profit YTD numbers.

To create the YTD view of Direct Profit, we will create a new measure with the following formula.

[stextbox id='grey']DirectProfit YTD = TOTALYTD(SUM(Data[Direct Profit]), 'Date'[Date])[/stextbox]

This formula evaluates the expression of some of the “Direct Profit” values from the “Data” table and returns the cumulative YTD numbers for the “Date” column.

The area chart represents the Direct Profit YTD numbers and is comparable across two years. The YTD trends across the two years are quite comparable for the months of February to June. However, it clearly shows a different trend for 2018 compared to the previous year for the months of July to October. The Direct Profit YTD from July to October 2018 is much lower than the corresponding period of 2017.

In addition, there is a sharp jump in Direct Profit for November and December 2018. Whereas, it flattens out for the same period in 2017. Apart from TOTALYTD, there are functions like TOTALMTD and TOTALQTD that can give the Month-to-Date and Quarter-to-Date values for any business metric.

Another, most commonly used time intelligence function in Power BI is SAMEPERIODLASTYEAR. It evaluates the business performance of the current period against the same period the previous year.

End Notes

In this article, we learned about how time intelligence plays an important role in evaluating business performance. Additionally, we also looked at a hands-on example of calculating the YTD numbers for a given KPI in Power BI.

To summarize, Power BI offers numerous time intelligence functions that help in reporting the performance of a business. Furthermore, unlike cell-based calculations in Excel, we can create measures in Power BI. Measures can evaluate a given time calculation on-the-go at any level of detail.

Let us know if you have any queries in the comments below.

Kaushik Roy Chowdhury 05 Aug 2020

Kaushik is a data science professional and a trainer with more than 7 years of experience working in the consulting domain and around 3 years of teaching experience.