How to create a fiscal year calendar in Power BI?

by

Here’s a question from a colleague:
In our HR department, we often need to report based on our company’s fiscal year, which runs from April 1st to March 31st. How can I create a custom calendar table in Power BI to support our fiscal year reporting?

Setup a fiscal calendar table in Power BI


Creating a custom calendar table with fiscal year calculations is crucial for accurate reporting in many organizations, especially when the fiscal year doesn’t align with the standard calendar year.

In Power BI, we can use DAX to create this custom table. In this post we will walk through the process of creating a comprehensive calendar table that includes both calendar and fiscal year information.


Create the Calendar Table (if it’s unavailable)

First, we’ll create a basic calendar table using DAX. We’ll assume we want to cover the years 2020 to 2025.

Here’s is the DAX code that you can use to create the custom table:

Custom Calendar =
ADDCOLUMNS(
CALENDAR(DATE(2020, 1, 1), DATE(2025, 12, 31)),
"Year", YEAR([Date]),
"Month Number", MONTH([Date]),
"Month Name", FORMAT([Date], "mmmm"),
"Day of Week", FORMAT([Date], "dddd"),
"Day Number", DAY([Date])
)

Add Fiscal Year Calculations


Now, let’s add columns for fiscal year calculations. We’ll assume the fiscal year starts on April 1st. Override the previous table definition with this DAX code:

Custom Calendar = 
ADDCOLUMNS(
ADDCOLUMNS(
CALENDAR(DATE(2020, 1, 1), DATE(2025, 12, 31)),
"Year", YEAR([Date]),
"Month Number", MONTH([Date]),
"Month Name", FORMAT([Date], "mmmm"),
"Day of Week", FORMAT([Date], "dddd"),
"Day Number", DAY([Date])
),
"Fiscal Year", IF(MONTH([Date]) >= 4, YEAR([Date]) + 1, YEAR([Date])),
"Fiscal Quarter", SWITCH(
TRUE(),
AND(MONTH([Date]) >= 4, MONTH([Date]) <= 6), 1, AND(MONTH([Date]) >= 7, MONTH([Date]) <= 9), 2, AND(MONTH([Date]) >= 10, MONTH([Date]) <= 12), 3, 4 ), 
"Fiscal Month Number", IF(MONTH([Date]) >= 4, MONTH([Date]) - 3, MONTH([Date]) + 9)
)

Here’s our table – which you can preview using the Power BI table view.


This extended version of our DAX code adds the following columns to our Custom Calendar table in the data model:

  • Fiscal Year: Increments by 1 if the date is in or after July.
  • Fiscal Quarter: Calculates the fiscal quarter (1-4) based on the month.
  • Fiscal Month Number: Reorders months so April is 1 and March is 12.

Using the Custom Calendar Table

After creating the custom fiscal year table, make sure to mark it as a date table in Power BI. Then, create relationships between this custom calendar and your fact tables in your data model.