How to Set a Custom Fiscal Year (October–September) in Power BI

After seven years building Power BI solutions for UK businesses, I've lost count of the number of times finance directors have asked why their "year-to-date" figures are wrong. The answer is always the same: Power BI is counting from January, but their business counts from April, September, or some other month entirely.

If you're working with UK organisations—and especially if you're in the education, charity, or public sector—you will encounter non-calendar fiscal years. Schools and universities typically operate September to August or October to September. Many charities align their financial year with grant cycles. Even UK corporations, while often using April–March for accounting, sometimes have peculiar legacy arrangements.

This guide is written from experience implementing Power BI for organisations across these sectors. I'll show you exactly how to build a Date Table that handles custom fiscal years, the DAX patterns I use in production, and the mistakes I see repeatedly—even from experienced Power BI developers.

What you'll learn

By the end of this guide, you'll have a production-ready Date Table with fiscal year support, understand how time intelligence functions work with custom calendars, and know how to avoid the common pitfalls that cause reporting errors.

In This Guide

Understanding UK Fiscal Years
Why Power BI Gets It Wrong by Default
Step 1: Create Your Date Table
Step 2: Add Fiscal Year Columns
Step 3: Add Fiscal Quarter Columns
Step 4: Add Fiscal Month Columns
Step 5: Create Sort Columns
Step 6: Mark as Date Table
Step 7: Use Fiscal Year in Measures
Common Mistakes from Real Projects
Adapting for Any Fiscal Year Start

Understanding UK Fiscal Years

The UK has more fiscal year conventions than any country I've worked with. Here's what you'll encounter:

UK Fiscal Year Reference

UK Tax Year: 6 April – 5 April (historical quirk from 1752 calendar change)
UK Corporation Tax: 1 April – 31 March (most companies)
Academic Year: 1 September – 31 August (schools/colleges)
Academic Year (alt): 1 October – 30 September (some universities)
Grant Year: Varies by funder (commonly 1 April or 1 October)

The UK tax year starting on 6 April is genuinely odd—it dates back to when Britain moved from the Julian to the Gregorian calendar in 1752, "losing" 11 days. The tax authorities didn't want to lose 11 days of tax revenue, so they adjusted the year end. It's a historical curiosity that still causes headaches today.

In practice, most UK businesses use 1 April as their financial year start for accounting simplicity. It aligns with calendar quarters (Apr-Jun, Jul-Sep, etc.) and makes year-end comparisons more straightforward. But if you're working in education or the charity sector, you're almost certainly dealing with September or October year starts.

Why Power BI Gets It Wrong by Default

Power BI's time intelligence functions—TOTALYTD, SAMEPERIODLASTYEAR, OPENINGBALANCEYEAR, and others—assume a calendar year starting 1 January. They're not configurable in any simple way. This is fundamentally an American product design decision that doesn't map well to international reporting requirements.

When a finance director asks for "year-to-date sales" and your Power BI report is counting from 1 January, but their financial year started 1 April (or 1 September), you have a problem. The report is technically correct—it's showing YTD from the calendar year—but it's wrong for the business context.

The critical mistake

I've seen experienced analysts try to work around this with complex CALCULATE filters and date arithmetic. It almost always breaks. The correct solution is simple: build a proper Date Table with fiscal columns and use it consistently. Everything else is a workaround that will fail eventually.

Step 1: Create Your Date Table

First, disable Auto Date/Time. This feature creates hidden date tables that conflict with your custom Date Table and cause confusing errors. Go to:

File > Options and Settings > Options > Current File > Data Load > Time Intelligence

Uncheck "Auto Date/Time for new files". Do this now—it will save you hours of debugging later.

Now create your Date Table. In Power BI Desktop, go to Modeling > New Table and paste this DAX:

Date =
VAR StartDate = DATE(2020, 1, 1)
VAR EndDate = DATE(2030, 12, 31)
VAR DateTable =
    ADDCOLUMNS(
        CALENDAR(StartDate, EndDate),
        "Year", YEAR([Date]),
        "Month", MONTH([Date]),
        "MonthName", FORMAT([Date], "MMMM"),
        "MonthShort", FORMAT([Date], "MMM"),
        "Quarter", "Q" & ROUNDUP(MONTH([Date])/3, 0),
        "QuarterNum", ROUNDUP(MONTH([Date])/3, 0),
        "DayOfWeek", WEEKDAY([Date]),
        "DayName", FORMAT([Date], "dddd"),
        "DayOfWeekNum", WEEKDAY([Date], 2),
        "IsWeekend", IF(WEEKDAY([Date], 2) >= 6, TRUE(), FALSE()),
        "WeekOfYear", WEEKNUM([Date], 1),
        "MonthYear", FORMAT([Date], "MMM yyyy"),
        "YearQuarter", FORMAT([Date], "yyyy") & "-Q" & ROUNDUP(MONTH([Date])/3, 0)
    )
RETURN
    DateTable
          

Why CALENDAR instead of CALENDARAUTO? I prefer explicit date ranges because I want to control exactly what dates are included. CALENDARAUTO infers from your data, which can cause issues if you have future-dated transactions or incomplete historical data. Set your range to cover your entire data span plus a buffer.

Pro tip: For production models, I typically create the Date Table in Power Query instead of DAX. The M-language approach is more performant for large date ranges and easier to debug. But the DAX approach above is perfectly adequate for most models and easier to explain.

Screenshot Placeholder

Power BI Desktop showing the Modeling tab with "New Table" selected. The formula bar displays the Date table DAX. The Fields pane shows the newly created Date table with its columns.

Step 2: Add Fiscal Year Columns

Now we'll add columns for an October–September fiscal year. I'll show you the production approach I use, which includes both numeric values (for calculations) and formatted display values (for reporting).

First, add a calculated column for the fiscal year number:

Fiscal Year =
IF(
    MONTH([Date]) >= 10,
    YEAR([Date]) + 1,
    YEAR([Date])
)
          

How this works: If the month is October (10) or later, we're in the next fiscal year, so we add 1 to the calendar year. Otherwise, we use the current calendar year. This means October 2024 through September 2025 will all have Fiscal Year = 2025.

Next, add a formatted display column. The exact format depends on your organisation's preference:

Fiscal Year Label =
"FY " & [Fiscal Year] - 1 & "/" & RIGHT([Fiscal Year], 2)

-- Examples:
-- FY 24/25 (October 2024 - September 2025)
-- FY 25/26 (October 2025 - September 2026)
          

Naming conventions matter

Some organisations prefer "2024/25" without the FY prefix. Others want "FY25" using the ending year only. I've even seen "F2025". Check with your finance team before finalising—they often have strong opinions on this.

For comparison visuals, you'll often want the previous fiscal year:

Previous Fiscal Year =
[Fiscal Year] - 1

Previous Fiscal Year Label =
"FY " & [Fiscal Year] - 2 & "/" & RIGHT([Fiscal Year] - 1, 2)
          

Step 3: Add Fiscal Quarter Columns

For an October–September fiscal year, quarters shift as follows:

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

Add these calculated columns:

Fiscal Quarter Num =
IF(
    MONTH([Date]) >= 10,
    ROUNDUP((MONTH([Date]) - 9) / 3, 0),
    ROUNDUP((MONTH([Date]) + 3) / 3, 0)
)

Fiscal Quarter =
"Q" & [Fiscal Quarter Num]

Fiscal Year Quarter =
[Fiscal Year] & "-" & [Fiscal Quarter]
          

Understanding the logic: For October onwards, we subtract 9 to map October→1, November→2, etc. For January onwards, we add 3 to map January→4, February→5, etc. Then ROUNDUP divides by 3 to group into quarters.

Screenshot Placeholder

The Date table in Data view, showing fiscal year columns. October 2024 shows Fiscal Year = 2025, Fiscal Quarter = Q1. The table clearly demonstrates the fiscal year mapping.

Step 4: Add Fiscal Month Columns

Fiscal months follow the same pattern as quarters—October is month 1, November is month 2, and so on:

Fiscal Month Num =
IF(
    MONTH([Date]) >= 10,
    MONTH([Date]) - 9,
    MONTH([Date]) + 3
)

Fiscal Month Name =
FORMAT(
    DATE(YEAR([Date]), [Fiscal Month Num] + 9, 1),
    "MMMM"
)

Fiscal Year Month =
[Fiscal Year] & "-" & FORMAT([Fiscal Month Num], "00")
          

The Fiscal Month Name formula is a clever trick: we create a date in month 10 (October) plus the fiscal month number minus 1, which maps fiscal month 1→October, 2→November, etc. Then FORMAT converts it to the month name.

Step 5: Create Sort Columns

Here's the mistake I see most often: people create month name columns but forget to set the sort order. Then their visualisations show April, August, December... alphabetically instead of January, February, March...

Common Error #1: Incorrect Month Sorting

I've reviewed production Power BI models where months are sorted alphabetically. Users didn't notice until someone asked why "April" appeared before "January" in their report. Always test your sort columns.

For calendar months, ensure your Month Name column is sorted by Month (the numeric month):

-- This column should already exist from Step 1
Month = MONTH([Date])
          

Then in Power BI Desktop: select MonthName → Column Tools tab → Sort by Column → select Month.

For fiscal months, sort Fiscal Month Name by Fiscal Month Num:

Screenshot Placeholder

Column Tools tab showing "Sort by Column" dropdown. Fiscal Month Name is selected, and Fiscal Month Num is chosen as the sort column. The dialog shows the sort configuration.

For quarters, sort Quarter by QuarterNum, and Fiscal Quarter by Fiscal Quarter Num.

Quick verification

After setting sort columns, create a simple table visual with Month Name and a value column. If months appear alphabetically, the sort isn't applied. You should see January, February, March... in order.

Step 6: Mark as Date Table

This step is critical and frequently missed. Power BI needs to know which table is your Date Table for time intelligence functions to work correctly.

Select the Date table in the Fields pane
Go to the Table Tools tab
Click "Mark as Date Table"
Select the Date column as the date column

Screenshot Placeholder

Table Tools tab with "Mark as Date Table" button highlighted. The dialog shows the Date table selected with the Date column chosen as the unique identifier.

Common Error #2: Forgetting to Mark as Date Table

This causes subtle errors. Your simple measures might work, but time intelligence functions return blanks or wrong values. The error message is unhelpful ("the column you selected isn't relevant to your data"). Mark your Date table immediately after creating it.

Step 7: Use Fiscal Year in Measures

With your Date Table configured, you can now write measures that respect your fiscal year. Here are the patterns I use in production models:

Fiscal Year-to-Date Sales:

Fiscal YTD Sales =
CALCULATE(
    [Total Sales],
    DATESYTD('Date'[Date], "09-30")
)
          

The second parameter ("09-30") tells DAX that the fiscal year ends on September 30. This is how you make time intelligence functions respect your custom fiscal calendar.

Fiscal Year Total Sales:

Fiscal Year Sales =
CALCULATE(
    [Total Sales],
    ALLSELECTED('Date'),
    'Date'[Fiscal Year] = MAX('Date'[Fiscal Year])
)
          

Previous Fiscal Year Sales (for comparison):

Previous Fiscal Year Sales =
CALCULATE(
    [Fiscal Year Sales],
    'Date'[Fiscal Year] = MAX('Date'[Fiscal Year]) - 1
)

-- Or using time intelligence
Previous Fiscal Year Sales (Alternate) =
CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR(
        DATESYTD('Date'[Date], "09-30")
    )
)
          

Fiscal Year-over-Year Growth:

Fiscal Year Growth =
IF(
    [Previous Fiscal Year Sales] = 0,
    BLANK(),
    DIVIDE(
        [Fiscal Year Sales] - [Previous Fiscal Year Sales],
        [Previous Fiscal Year Sales]
    )
)
          

Fiscal Quarter-to-Date:

Fiscal QTD Sales =
CALCULATE(
    [Total Sales],
    DATESQTD('Date'[Date]),
    'Date'[Fiscal Quarter Num] = MAX('Date'[Fiscal Quarter Num])
)
          

A note on performance

The ALLSELECTED pattern in Fiscal Year Sales is intentional—it ensures your visuals respond to slicers while still aggregating to the full fiscal year. In some models, you might prefer ALLEXCEPT instead. Test with your specific requirements.

Common Mistakes from Real Projects

After reviewing dozens of Power BI models across different organisations, I see the same mistakes repeatedly. These aren't theoretical—they cause real problems in production reports.

Mistake #3: Using Auto Date/Time

Power BI's Auto Date/Time feature seems convenient but it's actively harmful for production models. It creates hidden date tables that you can't customise, don't support fiscal years, and cause confusing errors when you have your own Date Table. Disable it globally in your options.

Mistake #4: Gaps in the Date Table

Your Date Table must be continuous with no gaps. I've seen models where CALENDAR was used with fixed dates, but the data included dates outside that range. Those transactions disappear from reports. Use CALENDARAUTO or ensure your date range covers all possible data dates plus buffer.

Mistake #5: Multiple Date Tables

Sometimes teams create separate Date tables for Order Date, Ship Date, Delivery Date, etc. Don't do this. Use role-playing dimensions—multiple relationships to the same Date table—instead. Multiple Date tables cause confusion and break time intelligence.

Mistake #6: Using Dates in Fact Tables for Time Intelligence

I've seen this pattern: CALCULATE([Sales], DATESYTD(Sales[OrderDate])). It works sometimes but it's fundamentally wrong. Time intelligence functions require a proper Date Table marked as such. Using date columns from fact tables will fail at fiscal year boundaries.

Mistake #7: Wrong Fiscal Year End in Time Intelligence

The "09-30" parameter in DATESYTD must match your fiscal year end. I've seen models with October–September fiscal years using "12-31" (calendar year end) in time intelligence functions. This gives you calendar year-to-date labeled as fiscal year-to-date. The numbers are wrong but nobody notices until year-end.

Mistake #8: Inactive Relationships Not Activated

When using role-playing dimensions (e.g., Order Date and Ship Date both relating to Date), only one relationship can be active. The other(s) must be activated explicitly with USERELATIONSHIP in measures. Forgetting this is a common source of wrong figures.

The audit checklist

Every time I review a Power BI model, I check: (1) Is Auto Date/Time disabled? (2) Is the Date Table marked? (3) Are month/quarter sort columns applied? (4) Do time intelligence measures use the fiscal year end date? (5) Are there gaps in the Date Table? These five checks catch 90% of Date Table issues.

Adapting for Any Fiscal Year Start

The October–September pattern is common in education, but you may need different fiscal years. Here are the formulas adapted for other common UK fiscal years:

April–March (UK Corporation Tax standard):

Fiscal Year =
IF(
    MONTH([Date]) >= 4,
    YEAR([Date]) + 1,
    YEAR([Date])
)

-- Time Intelligence parameter: "03-31"
          

July–June (Some grant-funded organisations):

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

-- Time Intelligence parameter: "06-30"
          

Generic pattern for any fiscal year start:

-- Replace 10 with your fiscal year start month (1-12)
Fiscal Year =
IF(
    MONTH([Date]) >= 10,
    YEAR([Date]) + 1,
    YEAR([Date])
)

Fiscal Month Num =
IF(
    MONTH([Date]) >= 10,
    MONTH([Date]) - 10 + 1,
    MONTH([Date]) + (12 - 10) + 1
)

-- For time intelligence, use the month END date
-- If fiscal year starts in October (10), it ends in September (9)
-- DATESYTD('Date'[Date], "09-30")
          

Quick reference table

Fiscal Year Start → DATESYTD Parameter:
January → "12-31" (or omit, it's the default)
April → "03-31"
July → "06-30"
September → "08-31"
October → "09-30"

Complete Date Table DAX (October–September)

Here's the complete production-ready Date Table DAX with all fiscal columns in one place. Copy this and adapt the date range as needed:

Date =
VAR StartDate = DATE(2020, 1, 1)
VAR EndDate = DATE(2030, 12, 31)
VAR DateTable =
    ADDCOLUMNS(
        CALENDAR(StartDate, EndDate),
        // Calendar columns
        "Year", YEAR([Date]),
        "Month", MONTH([Date]),
        "MonthName", FORMAT([Date], "MMMM"),
        "MonthShort", FORMAT([Date], "MMM"),
        "Quarter", "Q" & ROUNDUP(MONTH([Date])/3, 0),
        "QuarterNum", ROUNDUP(MONTH([Date])/3, 0),
        "DayOfWeek", WEEKDAY([Date]),
        "DayName", FORMAT([Date], "dddd"),
        "DayOfWeekNum", WEEKDAY([Date], 2),
        "IsWeekend", IF(WEEKDAY([Date], 2) >= 6, TRUE(), FALSE()),
        "WeekOfYear", WEEKNUM([Date], 1),
        "MonthYear", FORMAT([Date], "MMM yyyy"),
        "YearQuarter", FORMAT([Date], "yyyy") & "-Q" & ROUNDUP(MONTH([Date])/3, 0),

        // Fiscal Year columns (October-September)
        "Fiscal Year",
            IF(MONTH([Date]) >= 10, YEAR([Date]) + 1, YEAR([Date])),
        "Fiscal Year Label",
            "FY " & IF(MONTH([Date]) >= 10, YEAR([Date]) + 1, YEAR([Date])) - 1 &
            "/" & RIGHT(IF(MONTH([Date]) >= 10, YEAR([Date]) + 1, YEAR([Date])), 2),
        "Fiscal Month Num",
            IF(MONTH([Date]) >= 10, MONTH([Date]) - 9, MONTH([Date]) + 3),
        "Fiscal Month Name",
            FORMAT(DATE(YEAR([Date]),
                IF(MONTH([Date]) >= 10, MONTH([Date]) - 9, MONTH([Date]) + 3) + 9, 1), "MMMM"),
        "Fiscal Quarter Num",
            SWITCH(
                TRUE(),
                IF(MONTH([Date]) >= 10, MONTH([Date]) - 9, MONTH([Date]) + 3) <= 3, 1,
                IF(MONTH([Date]) >= 10, MONTH([Date]) - 9, MONTH([Date]) + 3) <= 6, 2,
                IF(MONTH([Date]) >= 10, MONTH([Date]) - 9, MONTH([Date]) + 3) <= 9, 3,
                4
            ),
        "Fiscal Quarter",
            "Q" & SWITCH(
                TRUE(),
                IF(MONTH([Date]) >= 10, MONTH([Date]) - 9, MONTH([Date]) + 3) <= 3, 1,
                IF(MONTH([Date]) >= 10, MONTH([Date]) - 9, MONTH([Date]) + 3) <= 6, 2,
                IF(MONTH([Date]) >= 10, MONTH([Date]) - 9, MONTH([Date]) + 3) <= 9, 3,
                4
            ),
        "Fiscal Year Quarter",
            IF(MONTH([Date]) >= 10, YEAR([Date]) + 1, YEAR([Date])) &
            "-Q" & SWITCH(
                TRUE(),
                IF(MONTH([Date]) >= 10, MONTH([Date]) - 9, MONTH([Date]) + 3) <= 3, 1,
                IF(MONTH([Date]) >= 10, MONTH([Date]) - 9, MONTH([Date]) + 3) <= 6, 2,
                IF(MONTH([Date]) >= 10, MONTH([Date]) - 9, MONTH([Date]) + 3) <= 9, 3,
                4
            )
    )
RETURN
    DateTable
          

Not Sure if Your Power BI Model is Set Up Correctly?

After building Power BI solutions for UK businesses across education, charity, and corporate sectors, I've learned that small Date Table issues often compound into major reporting problems. A Power BI audit can identify these issues before they cause confusion.

Book a Power BI Audit

Fiscal year configuration is one piece of a robust Power BI implementation. Digital Adaption provides comprehensive Power BI consultancy services for UK organisations, including data modelling, dashboard development, and team training.

If your Power BI reports are running slowly after implementing fiscal years, read our comprehensive guide on Power BI performance optimisation for practical fixes.

Need help with your Power BI implementation? Book a free 30-minute consultation to discuss your challenges and whether I can help.

Frequently Asked Questions

To change the fiscal year start date in Power BI, modify the Fiscal Year column formula in your Date Table. Replace the month number (currently 10 for October) with your desired start month. For example, for April start, use MONTH([Date]) >= 4. Also update the time intelligence parameter—April start requires "03-31" in DATESYTD functions.

The basic DAX formula for fiscal year in Power BI is: Fiscal Year = IF(MONTH([Date]) >= [StartMonth], YEAR([Date]) + 1, YEAR([Date])). Replace [StartMonth] with your fiscal year start month (10 for October, 4 for April, etc.). This assigns dates in/after the start month to the next calendar year, creating the correct fiscal year grouping.

Use TOTALYTD with a fiscal year-end date parameter: TOTALYTD([Measure], 'Date'[Date], "09-30"). The third parameter ("09-30") tells Power BI the fiscal year ends on September 30th. For April-March fiscal years, use "03-31". This ensures year-to-date calculations respect your fiscal calendar instead of the calendar year.

Power BI sorts text columns alphabetically by default, so month names appear in alphabetical order (April, August, December...) instead of chronological order. Fix this by creating a numeric month column and using Column Tools > Sort by Column to sort MonthName by the numeric Month column. Do the same for fiscal months.

Yes, but it requires careful design. Use role-playing dimensions (multiple copies of the same Date table with different fiscal year columns) or create separate fiscal year columns in a single Date Table (e.g., Fiscal Year Corporate, Fiscal Year Academic). Ensure your measures explicitly reference the correct fiscal year columns to avoid confusion.

CALENDAR requires explicit start and end dates, giving you full control over the date range. CALENDARAUTO automatically generates dates based on your data model's date columns. CALENDARAUTO is convenient but can cause issues if your data has future-dated transactions or incomplete historical data. I prefer CALENDAR for production models.

The "mark as date table" error typically occurs when your Date table has duplicate dates, missing dates (gaps), or the Date column isn't a Date data type. Ensure your Date column contains unique, continuous dates with no gaps. Verify the data type is Date (not DateTime or Text). Then select Table Tools > Mark as Date Table and select the Date column.

Create fiscal quarters using SWITCH logic: Fiscal Quarter = SWITCH(TRUE(), [Fiscal Month Num] <= 3, "Q1", [Fiscal Month Num] <= 6, "Q2", [Fiscal Month Num] <= 9, "Q3", "Q4"). This groups fiscal months into quarters. Always create a numeric Fiscal Quarter Num column for sorting and use it to sort the Fiscal Quarter text column.

Need Help With Your Power BI Implementation?

Whether you're setting up fiscal years, struggling with data modeling, or need a full Power BI implementation, I work with UK organisations to build robust, scalable reporting solutions.

Book a Free 30-Minute Consultation

How to Set a Custom Fiscal Year (October–September) in Power BI – Step by Step

Understanding UK Fiscal Years

Why Power BI Gets It Wrong by Default

Step 1: Create Your Date Table

Step 2: Add Fiscal Year Columns

Step 3: Add Fiscal Quarter Columns

Step 4: Add Fiscal Month Columns

Step 5: Create Sort Columns

Common Error #1: Incorrect Month Sorting

Step 6: Mark as Date Table

Common Error #2: Forgetting to Mark as Date Table

Step 7: Use Fiscal Year in Measures

Common Mistakes from Real Projects

Mistake #3: Using Auto Date/Time

Mistake #4: Gaps in the Date Table

Mistake #5: Multiple Date Tables

Mistake #6: Using Dates in Fact Tables for Time Intelligence

Mistake #7: Wrong Fiscal Year End in Time Intelligence

Mistake #8: Inactive Relationships Not Activated

Adapting for Any Fiscal Year Start

Complete Date Table DAX (October–September)

Not Sure if Your Power BI Model is Set Up Correctly?

Related Services

Frequently Asked Questions

Need Help With Your Power BI Implementation?