Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Change Fiscal Year in Power BI

Cody Schneider••8 min read

Power BI defaults to a standard calendar year running from January to December, but for many businesses, that simply doesn’t match reality. If your company’s fiscal year starts in April, July, or October, your reports will be out of sync. This article will show you exactly how to add a custom fiscal calendar to your Power BI model so your reports 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 a Standard Calendar Isn't Always Enough

Aligning your data with your fiscal year is essential for accurate financial reporting, performance tracking, and goal setting. For example:

  • Retailers often start their fiscal year in February, after the busy holiday season has ended.
  • Governments and non-profits frequently operate on a fiscal year that starts in July or October.
  • Seasonal businesses may align their fiscal calendar with their peak season to better measure performance.

When you try to analyze "quarterly sales" using a standard calendar, you're looking at the wrong set of months. Comparing Q3 performance year-over-year becomes meaningless if your real Q3 is actually October-December. By setting up a proper fiscal calendar, you ensure your time-based calculations and comparisons are correct, providing a true picture of your business health.

The Foundation: Your Date Table

Before you can define a fiscal year, you need a proper date table. This is non-negotiable for any serious analysis in Power BI involving time. A date table is a dedicated table that contains a continuous sequence of dates - one row for every day - covering the entire time span of your data.

While you can create one in many ways, using a simple DAX function is often the fastest. If you don't have one yet, follow these steps:

  1. In Power BI Desktop, go to the Data view.
  2. From the Home tab, click New Table.
  3. In the formula bar, enter this DAX formula. Adjust the start and end years to cover your data's range.
DimDate = CALENDAR(DATE(2020, 1, 1), DATE(2025, 12, 31))

This creates a table named DimDate with a single column called [Date]. Now, right-click on your new table's [Date] column, select Mark as date table, and confirm your selection. This tells Power BI to use your table for all time-based intelligence functions, giving you reliable results.

With this foundation in place, let's build out your fiscal year columns.

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.

Method 1: Adding a Fiscal Year with DAX

Using Data Analysis Expressions (DAX) is the most common and flexible way to add custom fiscal columns. DAX columns are calculated on the fly and respond to filters in your report. For this example, let's assume your company's fiscal year starts on July 1st.

Let's add the necessary columns one by one directly into our DimDate table.

Step 1: Create the Fiscal Year Column

The first step is to tell Power BI which fiscal year each date belongs to. Often, a fiscal year is named after the calendar year it ends in. For example, the period from July 1, 2023, to June 30, 2024, is typically called "Fiscal Year 2024" (or FY2024).

In the Data view, with your DimDate table selected, click New Column and enter this formula:

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

This formula checks the month number. If it's July (7) or later, it takes the calendar year and adds one. If it's before July, it just uses the current calendar year.

Step 2: Create a Fiscal Year and Quarter Column

Now, let's identify the fiscal quarter. This is where many users get stuck, as quarters "wrap around" from one calendar year to the next. For a July 1st start:

  • Q1: July, August, September
  • Q2: October, November, December
  • Q3: January, February, March
  • Q4: April, May, June

We’ll use two columns for an ideal setup: one for showing the quarter name (e.g., "FY2024 Q1") and a helper column to get the sort order right.

Click New Column again and add the following formula:

Fiscal Quarter =
VAR FiscalYear = IF( MONTH('DimDate'[Date]) >= 7, YEAR('DimDate'[Date]) + 1, YEAR('DimDate'[Date]) )
VAR QuarterNum = 
    SWITCH(
        TRUE(),
        MONTH('DimDate'[Date]) IN {7, 8, 9}, 1,
        MONTH('DimDate'[Date]) IN {10, 11, 12}, 2,
        MONTH('DimDate'[Date]) IN {1, 2, 3}, 3,
        4
    )
RETURN
    "FY" & FiscalYear & " Q" & QuarterNum

This DAX snippet first calculates the correct fiscal year, then uses a SWITCH function to assign a quarter number (1-4) based on the month. Finally, it combines them into a label like "FY2024 Q2."

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: Create Sort Columns

If you put your new Fiscal Quarter column into a chart, Power BI will sort it alphabetically ("Q1", "Q10", "Q2") which is wrong. You need to tell it how to sort correctly.

Create a numeric "helper" column that represents the unique order of your fiscal quarters over time. This makes sorting foolproof.

Back in your Data view click New Column, and enter:

Fiscal Quarter Sort = ('DimDate'[Fiscal Year] * 10) + QUOTIENT((MONTH('DimDate'[Date]) + 5), 3)

Let's walk through what this does: For an August 15, 2023 date:

  • The Fiscal Year would be 2024. 2024 * 10 = 20240.
  • The QUOTIENT formula translates the MONTH(8), which is fiscal quarter 1, and so on. (QUOTIENT(8+5, 3) = 1)
  • The result is 20241, which represents the first quarter of fiscal year 2024. Next quarter will be 20242, ensuring perfect chronological sorting.

Now, select the Fiscal Quarter column, go to the Column Tools tab in the ribbon at the top, and click Sort by Column. Choose Fiscal Quarter Sort from the dropdown. You’ve now instructed Power BI to sort your quarter labels correctly in all visuals!

Method 2: Using Power Query

If you prefer to handle data transformations before loading data into your model, you can do all of this in the Power Query Editor. The logic is the same, but you use the M language instead of DAX.

  1. On the Home tab, click Transform data to open the Power Query Editor.
  2. Select your Date table from the Queries pane on the left.
  3. Go to the Add Column tab and click Custom Column.

Step 1: Create the Fiscal Year Column

For your new Fiscal Year column, enter this M code. Again, we are assuming a July start month:

if Date.Month([Date]) >= 7 then Date.Year([Date]) + 1 else Date.Year([Date])

Click OK. After it's created, set its data type to Whole Number.

Step 2: Create the Fiscal Quarter

Click Custom Column again. Name it Fiscal Quarter and use this code:

let
    FiscalMonthStart = 7,  //Change this number to match your fiscal start month (e.g., 4 for April)
    FiscalYear = if Date.Month([Date]) >= FiscalMonthStart then Date.Year([Date]) + 1 else Date.Year([Date]),
    MonthOffset = Date.Month([Date]) - FiscalMonthStart,
    CorrectedMonth = if MonthOffset < 0 then MonthOffset + 12 else MonthOffset,
    QuarterNum = Number.RoundUp((CorrectedMonth + 1)/3)
in
    "FY" & Text.From(FiscalYear) & " Q" & Text.From(QuarterNum)

This code is more flexible, allowing you to set the start month by changing the FiscalMonthStart variable at the top. When done, set its data type to Text.

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.

Step 3: Don't Forget the Sort Column

Add one more Custom Column, name it Fiscal Quarter Sort, and use:

([Fiscal Year] * 10) + Number.RoundUp((if Date.Month([Date]) < 7 then Date.Month([Date]) + 6 else Date.Month([Date]) - 6)/3)

Ensure this is set as Whole Number. After completing this, click Close & Apply to return to Power BI. Then, repeat the sort-by-column process you did in the DAX method to relate the textual quarter label to its numeric sort key.

Using Your Fiscal Columns in Visuals and Reports

Now that the hard work is done, using your fiscal fields is easy.

  • Build a Fiscal Hierarchy: In the Data pane, drag and drop your Fiscal Quarter and Month Name (from your standard date table) underneath your Fiscal Year to create a natural drill-down hierarchy.
  • Visualize Your Data: Drag Fiscal Quarter onto the axis of a bar or line chart and pair it with a measure like Total Sales. Your visual will now be grouped and sorted by your custom fiscal calendar.
  • Time Intelligence Functions: You can even use DAX time-intelligence functions like SAMEPERIODLASTYEAR() by pairing them with your new Fiscal Year column to perform accurate year-over-year comparisons.

This small change makes all the difference, moving your business reporting from 'close enough' to completely accurate.

Final Thoughts

Setting up a custom fiscal calendar in Power BI is a critical one-time step that ensures all of your subsequent reporting is accurate and meaningful. Whether you prefer the dynamic flexibility of DAX or the structured transformations of Power Query, building out dedicated fiscal year and quarter columns in your date table is the key to aligning your analytics with your business operations.

While mastering Power BI setup takes time and careful attention to detail, what if you could sidestep the technical setup entirely? At Graphed, we handle complex configurations so you don't have to. You can simply connect your data sources and ask, "show me a comparison of product sales by fiscal quarter starting in July." We instantly build the real-time, professional dashboard for you, completely bypassing the need for DAX or M code and getting you straight to the insights you care about.

Related Articles