Introduction
Dans notre précédent articleIn our previous article, we explored how to calculate YoY (Year-over-Year) using Power BI and SQL. We briefly mentioned the importance of quarters for financial analysis, but fiscal quarters often vary from one company to another, which can introduce some complexity in the calculations. Today, we will see how to create a custom SQL function to calculate fiscal quarters based on your company's fiscal year start date.
Context
Fiscal quarters divide the financial year into four equal periods, allowing companies to structure their accounting and regularly analyze performance. However, not all companies start their fiscal year on January 1st. Thus, it is crucial to adapt quarter calculations based on the start date of the fiscal year.
Understanding the structure of fiscal quarters
Before creating the function, it is important to understand how quarters are generally structured:
- Quarter 1 (Q1): Months 1 to 3 of the fiscal year.
- Quarter 2 (Q2): Months 4 to 6 of the fiscal year.
- Quarter 3 (Q3): Months 7 to 9 of the fiscal year.
- Quarter 4 (Q4): Months 10 to 12 of the fiscal year.
Step 1: Create an SQL function for fiscal quarters
We will now write an SQL function that calculates the fiscal quarter based on the start date of your company's fiscal year. This function will take into account both the fiscal start date and the date of a transaction or event to determine which quarter it belongs to.
Two alternatives to calculate fiscal quarters
Before we dive into creating our SQL function, let’s explore two different approaches to calculating fiscal quarters.
Alternative 1: Using a series of conditions
The first approach is to use a series of conditions to determine the quarter based on the transaction month.
CREATE FUNCTION [dbo].[GetFiscalQuarter] (@Date DATE, @FiscalYearStartMonth INT)
RETURNS INT
AS
BEGIN
DECLARE @Month INT = MONTH(@Date);
DECLARE @FiscalQuarter INT;
SET @FiscalQuarter = CASE
WHEN @Month >= @FiscalYearStartMonth AND @Month < @FiscalYearStartMonth + 3 THEN 1
WHEN @Month >= @FiscalYearStartMonth + 3 AND @Month < @FiscalYearStartMonth + 6 THEN 2
WHEN @Month >= @FiscalYearStartMonth + 6 AND @Month < @FiscalYearStartMonth + 9 THEN 3
ELSE 4
END;
RETURN @FiscalQuarter;
END;
Alternative 2: Using modulo for a simplified calculation
The second approach uses a more concise method based on the modulo operator to calculate the quarter. This method is simpler and more efficient.
CREATE FUNCTION [dbo].[GetFiscalQuarter] (@InputDate DATE, @FiscalYearStartMonth INT)
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @Quarter INT;
SET @Quarter = (MONTH(@InputDate) - @FiscalYearStartMonth + 12) % 12 / 3 + 1;
RETURN CAST(@Quarter AS VARCHAR(1));
END;
Choosing the approach: Why use modulo?
Although both methods are valid, we will opt for the function using modulo due to its simplicity and efficiency. Modulo reduces the number of required conditions and makes the code more compact, while still being accurate for calculating fiscal quarters.
Step 2: Use the function in queries
We will now apply this function to our data using the Sales.SalesOrderHeader table from the AdventureWorks2022 database, assuming the fiscal year starts in April (month 4).
SELECT
OrderDate,
dbo.GetFiscalQuarter(OrderDate, 4) AS FiscalQuarter
FROM
Sales.SalesOrderHeader;
Step 3: Visualizing the results
After executing the query, let’s examine the results. You should now see a table with the OrderDate and the associated fiscal quarters (FiscalQuarter).
Step 4: Add the fiscal quarter as a dimension in Power BI
Now that we have created our SQL function to calculate fiscal quarters, let’s integrate it into our Power BI report from the previous article on calculating YoY. This will allow us to analyze the data not only by year and month but also by fiscal quarter.
1. Integrate an SQL query
Add the SQL function (SQL Source) we created previously into Power BI. For example:
SELECT
CASE
WHEN MONTH(OrderDate) >= 4 THEN YEAR(OrderDate)
ELSE YEAR(OrderDate) - 1
END AS FiscalYear,
MONTH(OrderDate) AS [Month],
dbo.GetFiscalQuarter(OrderDate, 4) AS FiscalQuarter,
OrderDate,
TotalDue
FROM
Sales.SalesOrderHeader;
This example calculates the fiscal year (FiscalYear) and adds the fiscal quarter (FiscalQuarter) as dimensions in the query results, assuming the fiscal year starts in April (month 4).
2. Integrate the data into Power BI: Add a matrix visual
- Place FiscalQuarter in the rows, along with FiscalYear, Month, and OrderDate.
- Add the TotalDue value.
3. Analyze data by fiscal
quarter
By adding the fiscal quarter dimension, you can now analyze performance not only by month and year but also by quarter. This provides a more detailed overview and helps you better understand seasonal and quarterly trends.
Conclusion
In this article, we explored two methods for calculating fiscal quarters in SQL. We started with a simple approach using modulo to create a flexible and efficient function. Thanks to this SQL function, you can now reliably calculate fiscal quarters regardless of your company’s fiscal year start month.
In our next article, we will continue along this path and explore how to automate financial reporting using SQL views. These views will help simplify and standardize the generation of recurring financial reports, saving you time and improving the accuracy of your analyses.
I hope this article has provided you with practical insights that you can apply to your projects. Feel free to share your questions or experiences in the comments!