Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Get Fiscal Year from Date in Power BI

Cody Schneider••7 min read

Working with dates in Power BI is usually straightforward until your company's financial year doesn't line up with the calendar year. Suddenly, simple time-based reports become a tricky puzzle. This article will guide you step-by-step through creating and using fiscal year calculations in Power BI with DAX, so you can build reports that accurately reflect your business cycles.

GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

Why Standard Calendar Years Don't Always Work

Power BI's built-in date functions are designed around the standard calendar year, running from January 1st to December 31st. This is perfect for many scenarios, but it's a major roadblock if your business operates on a fiscal year - a 12-month accounting period that doesn't start in January.

Many organizations use fiscal years for strategic reasons:

  • Retail companies often start their fiscal year in February to completely separate the holiday shopping season (October-January) into its own final quarter.
  • Governments and non-profits commonly operate on schedules that end with major funding or legislative periods, such as a fiscal year ending in June or September.
  • Seasonal businesses, like those in agriculture or tourism, might align their fiscal year with their peak operational season.

When your reports show "2023 Revenue," but your business's "2023 year" actually ran from July 1, 2022, to June 30, 2023, you create confusion. Stakeholders can't accurately assess performance, year-over-year comparisons become misleading, and your data fails to tell the correct story. To solve this, you need to teach Power BI how to think in your company's specific timeframe.

Getting Started: Your Date Table is Key

Before you write a single fiscal calculation, you need a proper Date table. Relying on the default date hierarchies that Power BI automatically creates is not a best practice for serious reporting. A dedicated Date table acts as a single, authoritative reference for all things time-related in your data model.

Why is a Date table so important?

  • It centralizes all your date and time logic, ensuring consistency across your entire report.
  • It's required to properly use Power BI's powerful time intelligence functions like DATESYTD, SAMEPERIODLASTYEAR, etc.
  • It allows you to add custom columns for fiscal periods, work weeks, holidays, and more.

If you don’t already have a Date table, you can create one easily with DAX.

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

How to Create a Simple Date Table

  1. In Power BI Desktop, navigate to the Data View (the table icon on the left).
  2. In the Home tab of the ribbon, click New Table.
  3. Enter the following DAX formula. CALENDARAUTO() automatically scans your data model for the earliest and latest dates used and builds a complete calendar between them.
Dates = CALENDARAUTO()

Press Enter. You'll now have a single-column table named 'Dates' with a continuous list of dates. Now, let's add some basic columns that we'll need. To add a new column, select your 'Dates' table, and in the Table tools tab, click New Column.

Year Column:

Year = YEAR('Dates'[Date])

Month Number Column:

Month Number = MONTH('Dates'[Date])

Month Name Column:

Month Name = FORMAT('Dates'[Date], "mmmm")

With our foundation set, we're ready to add fiscal columns.

Creating a Fiscal Year Column Using DAX

For our main example, we'll assume a common fiscal calendar where the year begins on July 1st. This means any date from July onward belongs to the next calendar year's fiscal period. For example, July 15, 2024, is part of Fiscal Year 2025.

Step 1: Write the DAX Formula

The logic we need to implement is this: if the month of a date is July (month 7) or later, the fiscal year is the calendar year plus one. Otherwise, it's just the calendar year. A simple DAX IF statement handles this perfectly.

With your 'Dates' table selected, click New Column and enter this formula:

Fiscal Year = 
IF(
    MONTH('Dates'[Date]) >= 7,
    YEAR('Dates'[Date]) + 1,
    YEAR('Dates'[Date])
)

Step 2: Understanding the Formula

Let's break down exactly what this formula is doing:

  • IF(...): This is the standard conditional function in DAX. It checks a condition and returns one value if TRUE and another if FALSE.
  • MONTH('Dates'[Date]) >= 7: This extracts the month number from the date (January=1, February=2, etc.) and checks if it's July (7) or greater.
  • YEAR('Dates'[Date]) + 1: If the condition is TRUE, it takes the calendar year and adds 1. For example, August 10, 2024, yields 2024 + 1 = 2025, correctly assigning it to FY2025.
  • YEAR('Dates'[Date]): If the condition is FALSE, it simply returns the current calendar year, e.g., April 5, 2024, gives 2024, which is FY2024.
GraphedGraphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Watch Graphed demo video

Step 3: Verification and Formatting

After adding the column, it's a good idea to check some key dates:

  • June 30, 2024 — Fiscal Year: 2024
  • July 1, 2024 — Fiscal Year: 2025
  • December 31, 2024 — Fiscal Year: 2025

If these match expectations, your fiscal year column works correctly! Finally, select the new Fiscal Year column, go to the Column tools tab, set Data type to Whole Number, and ensure the comma separator is turned off for clean formatting.

Building Other Useful Fiscal Columns

A fiscal year is just the beginning. To build truly insightful reports, you also need fiscal quarters and fiscal months. This requires a little more DAX but follows a similar logical approach.

Calculating Fiscal Month Number

First, create a fiscal month number where July becomes 1, August becomes 2, and so on. Regular month numbers won't do because they start in January.

Create a new column with this formula:

Fiscal Month Number = 
VAR FiscalStartMonth = 7
RETURN
MOD( MONTH('Dates'[Date]) - FiscalStartMonth + 12, 12 ) + 1

This formula uses the MOD function to shift month numbers, ensuring July becomes 1, August 2, ..., June 12.

Calculating Fiscal Quarter

Now, with the fiscal month number, calculating the quarter is straightforward. Instead of nested IFs, the SWITCH function provides a cleaner approach.

Create a new column for the fiscal quarter text:

Fiscal Quarter =
VAR CurrentFiscalMonth = 'Dates'[Fiscal Month Number]
RETURN
"Q" & SWITCH(
    TRUE(),
    CurrentFiscalMonth IN {1, 2, 3}, 1,
    CurrentFiscalMonth IN {4, 5, 6}, 2,
    CurrentFiscalMonth IN {7, 8, 9}, 3,
    4  -- For months 10, 11, 12
)

This assigns Q1 to months 1-3, Q2 to months 4-6, and so on.

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Creating Sort-Friendly Display Columns

Power BI sorts text columns alphabetically by default, which can misalign the fiscal quarters (Q10 before Q2). To fix this, create a combined label and a numeric sort column.

1. Create the Display Column:

FY-Quarter = "FY" & 'Dates'[Fiscal Year] & " - " & 'Dates'[Fiscal Quarter]

This produces labels like "FY2025 - Q1".

2. Create the Sort Column:

FY-Quarter Sort = ('Dates'[Fiscal Year] * 10) + QUOTIENT('Dates'[Fiscal Month Number]-1, 3)

This numeric column can be used for sorting.

3. Apply the Sort

Go to the Data View, select FY-Quarter, then in the Column tools tab, choose Sort by column and select FY-Quarter Sort. Now, the quarter labels will sort chronologically in visuals.

Using Your Fiscal Date Columns in Reports

With your augmented Date table, you can now create accurate fiscal reports:

  • Visuals: Drag Fiscal Year or FY-Quarter onto axes to group metrics by fiscal periods.
  • Slicers & Filters: Add Fiscal Year as a slicer to enable period-based filtering.
  • Mark as Date Table: In the Data View, select your 'Dates' table. In the Table tools tab, click Mark as date table and choose your Date column to enable Power BI's time intelligence features.

Final Thoughts

By building out a dedicated Date table with custom DAX columns, you’ve taken control of how time is represented in your Power BI reports. Now you can analyze performance, track goals, and make data-driven business decisions aligned with your company's fiscal calendar, avoiding the confusion of standard calendar years.

Learning DAX and structuring data models are powerful skills, but they can be complex. That’s why we built Graphed to be your AI data analyst. Instead of writing formulas, you can connect your data and ask in plain English: "Show me a comparison of revenue by fiscal quarter for the last two years." Graphed instantly creates the interactive dashboard, handling complex calculations behind the scenes.

Related Articles