Calculate Percentage Growth Over Time with Power BI


By:   |   Updated: 2020-06-23   |   Comments   |   Related: More > Power BI

Problem

Many experts have demonstrated how to calculate Time Intelligence in Power BI over the years including Year-on-Year, Month-on-Month, Week-on-Week and so on. These all can be grouped into what is known as "Period-on-Period", which is a technique used in business analytics to measure a value in the current date form as compared to same date form historically. Depending on the dataset or business requirements being analyzed, the end goal is always to get a view of the historical data in a way that the same amount of time has passed in the two compared periods.

Solution

For the purpose of this tip, the data source used for this work is a Power BI dataset which holds a table for the Tenant Product Usage and Activity. There is no calendar dimension table so we would need to create one later.

For this demo, we will calculate the rate of growth in the usage of each Microsoft Office 365 applications (OneDrive, SharePoint, Teams, Outlook, etc.) overtime. This will show the adoption of individual products within the organization as well as when there has been a spike or drop in active users overtime. This tip will concentrate on Month-on-Month date form and utilize the Office 365 adoption dataset for the demo.

To be able to create the rate of growth or increase in the usage of each O365 applications we need to first do the following:

  1. Create a Date dimension table as without this the Time Intelligence functions would not work.
  2. Create a measure for Current Usage.
  3. Create a measure for Previous Month Usage.
  4. Create a measure for Usage Difference.
  5. Create another measure for Rate of Growth or Increase in Usage.

What we are trying to achieve in the end is summarized in the formula as follows:

Month-on-Month Usage Growth = (Current Usage – Previous Month Usage) X 100 / Previous Month Usage.

So, let's demonstrate these in a series of steps, but I will not go into detail on how to create a Power BI dataset in Power BI Service. Your dataset could come from any other source like SQL database, Azure SQL database, Salesforce, SharePoint, etc.

Step 1: Create a Date dimension table.

There are several techniques for creating a date dimension table (a.k.a. Calendar Table) in Power BI. However, I prefer this technique:

<table output>=Calendar(<start date>, <end date>)

Calendar = CALENDAR(DATE(2016,1,1),DATE(2022,12,31))			

Or, to prevent hard coding your dates, you could dynamically set the start and end dates as follows:

Calendar = CALENDAR(FIRSTDATE(TenantProductUsage[ContentDate]),LASTDATE(TenantProductUsage[ContentDate]) 

Link up the Calendar table with the TenantProductUsage table on the Dates to ContentDate on a 1:* (one to many relationship) as shown below. You can add more columns as it suits your project:

Power BI Modelling page

Step 2: Create a measure for Current Usage

The Current Usage is derived by summing the "ActiveUserCount" field in the "TenantProductUsage" table as shown below:

Current Usage = SUM(TenantProductUsage[ActiveUserCount])
DAX calculation

Step 3: Create a measure for Previous Month Usage

To derive the Previous Month Usage, I used the time intelligence function "PREVIOUSMONTH" as shown below:

PriorMonthActiveUsers=CALCULATE(SUM(TenantProductUsage[ActiveUserCount]),PREVIOUSMONTH(Calendar[Date]))
DAX calculation

Step 4: Create a measure for Usage Difference

To do this I will apply the use of variables in DAX just for an illustration as I can split the calculations where possible, but variables make the DAX code much simpler to read as shown below:

Usage Difference = 
      VAR _CurrentMonthUsage = TenantProductUsage[Current Usage]
      VAR _PreviousMonthUsage = TenantProductUsage[PriorMonthActiveUsers]
      VAR _Result = _CurrentMonthUsage -  _PreviousMonthUsage
      RETURN _Result
DAX calculation

Step 6: Create measure for Rate of Growth or Increase in Usage

This is derived by dividing Usage Difference by PriorMonthActiveUsers so we can get the rate of increase in usage as follows:

Usage Growth = DIVIDE ([Usage Difference], [PriorMonthActiveUsers], 0)CALENDAR(DATE(2016,1,1),DATE(2022,12,31))
DAX calculation

Visualize on Reports

Having done these steps successfully, we can now visualize the created measures in Power BI to see and compare the output. If visualized on a table, you should have something like below:

Power BI visualisation
Table visual

When we show these on other visuals and apply a slicer using a category like the different O365 applications, we should be able to dynamically visualize the rate of increase for each application as shown below:

Line and Clustered Column Chart
Waterfall Chart
Next Steps
  • Learn more about how to create Date tables here.
  • You can get more information on creating Power BI relationships here.
  • You can get more information on how to use time intelligence functions in DAX here.
  • Try this tip out in your own dataset.


Last Updated: 2020-06-23


get scripts

next tip button



About the author
MSSQLTips author Kenneth A. Omorodion Kenneth A. Omorodion is a Data Analytics and BI professional mostly in Microsoft BI stack of tools. He is a BI Consultant with Avanade.

View all my tips





Comments For This Article





download


Recommended Reading

Using Power BI with JSON Data Sources and Files

Calculating MTD, QTD, YTD, Running and Cumulative Total in Power BI

Power BI Best Practices

Using Parameters in Power BI

Creating a Rolling Average Graph with Power BI





get free sql tips
agree to terms


Learn more about SQL Server tools