When doing analysis, it is 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 and 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.
Table of contents
What is Same Day Growth?
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 compare sales, fairer companies make working-day corrections. In case last year has more working days, one adjusts last year’s numbers 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.
Calculating Same Day Growth
The next videos show how you can adjust for these differences to make comparisons fairer. In the end, you should be able to compute the same-day growth values.
Single Dimension
The below video shows the elements you need in your data model to calculate a working-day correction.
Two Dimensions: Working Day Correction
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!
Multi-level Hierarchy Calculations
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 country. Yet, the method used in the 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.
Conclusion
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!