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.
Mark the fiscal calendar as the date table
After creating the fiscal calendar, validate it before building measures on top of it. The Date column should contain one row per day, no blanks, no duplicates, and a continuous range that covers every date in your fact tables. If the fact table has transactions outside the calendar range, time intelligence measures can return blanks or partial totals that are difficult to spot in visuals.
In Power BI Desktop, mark the table as a date table and select the actual Date column, not a formatted text label. Then create relationships from fact date columns to the fiscal calendar and test one fiscal month, quarter, and year total against a simple filtered table. This quick validation catches fiscal-year boundary mistakes before the model reaches report users.
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.