How to Calculate Same Day Growth in Power BI

Calculate Same Day Growth

When doing analysis it important that numbers are comparable. After all, you want to know how a company performs. And these comparisons are not always fair. And some elements can distort comparability. For example seasonality, incidental expenses but also the amount of working days. This article focuses on how to calculate Same Day Growth in Power BI. Below you find an introduction and three videos on how to calculate same day growth.

Introduction

Imagine a company that reports sales for 5 countries. They compare the current month’s numbers to the month last year. Differences in the number of working days between periods can significantly affect growth numbers. To make comparing sales fairer companies make working-day corrections. In case last year has more working days, one adjusts the numbers of last year by a factor.

Looking at working days three elements impact the amount of working days in a month. Firstly, the month this year may have a different number of days than last year’s month. February generally has 28 days and 29 in a leap year. Secondly, months don’t always start on the same day. This can result in a different number of working days in the current month. And thirdly, public holidays may occur on working days or weekends.

The next videos show how you can adjust for these differences to make comparisons fairer.

Calculate Same Day Growth with single dimension

Below video shows the elements you need in your data model to calculate a working-day correction.

Apply working-day correction on two dimensions

Calculating the working-day ratio and applying it to last year’s number is relatively easy for a single country. The challenge appears when trying to calculate correct numbers for a region while each country has a different working day ratio. This video shows a method on how you can use SUMX to do just that!

Ensure Same Day Growth Calculation is correct in multi-level hierarchies

In the last video you learned how to make use of SUMX to generate the correct numbers for subtotals. We split the country by months or split the month by countries. Yet, the method used in last video had a downside. For each of the two layouts, a different measure is required. So using the measure requires some thought of when it will work correctly.

In the video below you will learn how to use a virtual table. By combining SUMX and CROSSJOIN you are able to calculate the correct Same Day Growth numbers, no matter the setup of your data.

Wrap up

I hope this video series gave you an idea of how you can combine SUMX and virtual tables to perform complex aggregations. By spending some time setting up a measure correctly up front, you prevent yourself some headaches in the end. Your users will be thankful.

Please feel free to drop a comment with questions or suggestions, I would love to hear from you. And thanks for the support!

Rick de Groot
About Rick de Groot

Rick is the founder and editor of Excel Gorilla. He believes learning is one of the great pleasures in life and wants to share his knowledge to help you improve your skills.

Learn more about him here, connect with him on Twitter, Facebook and LinkedIn and subscribe to his YouTube Channel.

Leave a comment