Calculating YoY (Year-over-Year) in DAX and SQL

Introduction

Today, for my first in-depth article, I present to you the intricacies of a commonly used measure in business intelligence: YoY, or "Year-over-Year." This measure is essential for evaluating a company's performance on an annual basis, by comparing the results of a given period with those of the same period the previous year. In this article, we will explore how to calculate YoY using DAX in Power BI, and then we'll see how to obtain it directly in a SQL database.

Context

For the sake of this demonstration, we will use Microsoft's AdventureWorks2022 sample database. This database is an excellent tool for practicing and testing the concepts we will cover. If you wish to follow the steps at home, you can download and install this database from the official Microsoft website.​

First Approach: Using DAX

DAX (Data Analysis Expressions) is a formula language used in Power BI to create custom calculations. In this first approach, we will demonstrate how to transform data directly in Power BI from a fixed source such as a CSV, Excel, or Access database. For this example, we will connect to the SalesOrderHeader table from the AdventureWorks2022 database.

Steps

1. Add a SQL Data Source: Start by adding a SQL data source in Power BI and import the OrderDate and TotalDue columns from the SalesOrderHeader table. You can rename the query for clarity ("SalesOrderHeader").

2. Add a Year Column: Use the OrderDate column to create a new Year column that extracts the year from the order date.

3. Add a Month Column: Similarly, create a Month column that extracts the month from the order date.

4. Add a Previous Year Column: Create a PreviousYear column by subtracting 1 from the Year value.

5. Group the Data: Use the "Group By" feature to group the data by Year and Month, calculating the sum of TotalDue for each group.

6. Merge: Perform a self-join using the PreviousYear=Year and Month=Month columns to obtain the data from the previous year.

Note: You can rename the prefix of the join result (e.g., "YoY" in this case).

7. Expand: Expand the YoY.Total column to display the Total of the previous year's TotalDue.

Visualize the Data in Power BI

After preparing our data with the Year, Month, and YoY.Total columns, it's time to visualize this information in Power BI.

Steps

1. Add a Measure:

• Use Power BI's "Quick Measure" feature to create a "Percentage Difference" measure. This measure will calculate the percentage difference between Total and YoY.Total.

2. Add a Table Visual:

• Create a Table visual in Power BI.
• Place Year in the Rows.
• Add the Total, YoY.Total, and Total % Difference columns in the Values.

This will give you a clear view of annual revenues, previous year's amounts, and year-over-year percentage changes.

But What Happened with 2013?

Upon closer inspection of the data, you may notice something strange: the YoY amount for 2024 is not the same as that for 2013. What happened?

If we examine the data table as a whole, we find that there are no entries after June 2014. This means that the months from July to December 2013 are not compiled in our calculations. This obviously skews the year-over-year comparison.

How to Fix It

To resolve this issue, we need to modify our join method to include all the data, even those that might be missing in one of the years being compared.

Correction Steps

1. Go Back to Step 6:

• Change the Join Kind to a Full Outer Join rather than the default Left Outer Join. This will include all Year-Month pairs, even if they are missing in one of the years.

2. Keep YoY.Year and YoY.Month:

• After expanding YoY.Total, be sure to also keep YoY.Year and YoY.Month in your table.

3. Add Columns to Fix Year and Month:

• o Add a custom column to compare YoY.Year and Year, then create a conditional YearFixed column that contains the correct value:

• • YoY.Year1 = YoY.Year + 1

• • YearFixed = If Year equals null then YoY.Year1, else Year

• o Also, add a conditional MonthFixed column with the following logic:

• • MonthFixed = if Month is null then YoY.Month else Month

4. Replace Year and Month:

• o Replace the Year and Month columns in your final table with YearFixed and MonthFixed to correct the anomalies.

Why Include the Months?

In our approach, we chose to include the months in our YoY calculations. Why? Including the months allows for finer granularity in data analysis, which is crucial for companies with significant seasonal variations. By analyzing the results month by month, it becomes possible to detect trends specific to certain periods of the year, not just overall annual trends.

However, this approach introduces a particular challenge: when data is incomplete for certain months, as was the case for 2013, it can skew year-over-year comparisons.

The Problem with 2013 vs. 2014

In our example, we noticed an anomaly when comparing the results of 2013 and 2014. The YoY amount for 2024 was not the same as that calculated for 2013. This discrepancy was due to the fact that our data did not contain entries after June 2014, which means that the months from July to December 2013 were not compiled. As a result, the comparison for 2014 was biased by these missing data.

How to Visualize by Month

To avoid this kind of problem, we modified our approach by using a Full Outer Join during the data join, which allowed us to retain all months, even those without data, and to fill in these missing periods appropriately.

Once the data is properly aligned, you can visualize it by month in Power BI:

1. 1. Create a Matrix Visual:

• o Place YearFixed and MonthFixed in the Rows.
• o Add the TotalDue, YoY.TotalDue, and Percent Change columns in the Values.

2. Analyze Monthly Trends:

• o This visual will allow you to see month-to-month variations and identify seasonal trends, which can be crucial for certain business analyses.

Why Not Quarters?

We could have also included quarters in our analysis. However, since fiscal years vary from one company to another, this would have introduced additional complexity. We will return to this topic in our next article, where we will show you how to create a function to calculate quarters based on the start of your company's fiscal year.

Deuxième Approche : SQL (MSSQL)

If you have access to execute SQL queries directly on your database, it is often better to transform your data upstream. This approach can significantly improve performance, especially when working with large volumes of data.

Depending on your version of SQL Server, you may be able to use the LAG function, which greatly simplifies year-over-year calculations. For earlier versions, we will also demonstrate how to proceed with a more traditional approach, and how this impacts server resources.

Using LAG (Supported Versions)

For versions of SQL Server 2012 and later, you can use the LAG function as follows:

SELECT     YEAR(OrderDate) AS Year,     SUM(TotalDue) AS CurrentYearRevenue,     LAG(SUM(TotalDue), 1) OVER (ORDER BY YEAR(OrderDate)) AS PreviousYearRevenue,     ((SUM(TotalDue) - LAG(SUM(TotalDue), 1) OVER (ORDER BY YEAR(OrderDate))) / LAG(SUM(TotalDue), 1) OVER (ORDER BY YEAR(OrderDate)) * 100) AS YoY_Percent_Change FROM Sales.SalesOrderHeader GROUP BY YEAR(OrderDate) ORDER BY Year;

Without LAG (Earlier Versions)

For versions prior to SQL Server 2012, here is an alternative method to calculate YoY:

SELECT     CurrentYear.Year,     CurrentYear.Revenue AS CurrentYearRevenue,     PreviousYear.Revenue AS PreviousYearRevenue,     ((CurrentYear.Revenue - PreviousYear.Revenue) / PreviousYear.Revenue) * 100 AS YoY_Percent_Change FROM     (SELECT YEAR(OrderDate) AS Year, SUM(TotalDue) AS Revenue      FROM Sales.SalesOrderHeader      GROUP BY YEAR(OrderDate)) AS CurrentYear LEFT JOIN     (SELECT YEAR(OrderDate) + 1 AS Year, SUM(TotalDue) AS Revenue      FROM Sales.SalesOrderHeader      GROUP BY YEAR(OrderDate)) AS PreviousYear ON CurrentYear.Year = PreviousYear.Year ORDER BY CurrentYear.Year;

Performance Analysis

Maintenant, regardons la différence en termes de ressources et de temps d'exécution au niveau du serveur. Nous avons utilisé SQL Server Profiler pour capturer les détails d'exécution de ces deux requêtes.

As can be seen from the results, without the LAG function, the time and resources required to execute the query are significantly increased. Imagine the impact on a server when the data volume is high. This older approach can become a bottleneck in terms of performance.

Conclusion

In this article, we explored two methods to calculate YoY (Year-over-Year) in Power BI and SQL Server. We saw how DAX in Power BI offers flexibility for fixed data sources, and how SQL, with or without the LAG function, allows you to transform data upstream to improve performance.

Nous avons également discuté des avantages d'inclure des analyses par mois et comment surmonter les défis lorsque les données sont incomplètes. Enfin, bien que nous ayons choisi d'analyser les données par mois, il est important de noter que l'analyse par trimestre pourrait être tout aussi pertinente. Cela étant dit, les trimestres fiscaux varient d'une entreprise à l'autre, ce qui introduit une complexité supplémentaire. Mais ne vous inquiétez pas, nous reviendrons sur ce sujet dans notre prochain article, où je vous montrerai comment créer une fonction pour calculer les trimestres relativement au début de l'année fiscale de votre entreprise.

J'espère que cet article vous a fourni des insights utiles pour optimiser vos analyses et améliorer l'efficacité de vos calculs de KPI. N'hésitez pas à partager vos questions ou vos expériences en commentaire!