Power BI Time Intelligence

Power BI Fiscal YTD (TOTALYTD) Explained: Correct Year-to-Date for Non-Calendar Years

Fix wrong YTD figures in your Power BI reports. Learn the correct way to calculate fiscal year-to-date using TOTALYTD, DATESYTD, and custom DAX patterns for UK finance teams.

18 min read Last updated: 30 December 2025 By Matty, Senior Business Analyst

The real problem: I've lost count of the number of client models where YTD is quietly wrong. January shows calendar YTD (Jan–Jan), not fiscal YTD (Apr–Jan or Oct–Jan). The finance team trusts the numbers. They're used in board packs. And they're wrong.

If you're working with a non-calendar fiscal year—whether that's the UK tax year (6 April–5 April), an October–September year, or any other custom period—Power BI's default time intelligence will give you incorrect YTD figures unless you configure it correctly.

This guide shows you exactly how to implement correct fiscal year-to-date calculations in Power BI, step by step, with production-ready DAX you can copy into your model today.

Why YTD is Often Wrong: The Silent Risk in Finance Reports

Before we dive into the solution, it's worth understanding why this problem is so common—and why it often goes unnoticed.

The issue typically stems from one of three scenarios:

Default calendar assumptions: Someone uses TOTALYTD(Sales[Amount], 'Date'[Date]) without specifying a fiscal year-end date. Power BI assumes a 31 December year-end and calculates YTD from 1 January.
Incomplete date table: The Date table exists but isn't properly marked as a Date table, or lacks the necessary fiscal year columns to support correct time intelligence.
Copy-paste errors: Measures are copied from calendar-year models into fiscal-year models without updating the year-end parameter.

Warning: The risk isn't just wrong numbers—it's trusted wrong numbers.

When YTD is calculated from January but your fiscal year starts in April, January YTD shows January's actuals (correct), but May YTD shows Jan–May instead of Apr–May. The difference compounds month by month, and by December you're comparing a 12-month total against a 9-month total without realising it.

Prerequisites: What You Need Before Implementing Fiscal YTD

Before implementing fiscal YTD measures, ensure your model meets these requirements:

A proper Date table: Continuous dates, no gaps, marked as a Date table in Power BI
Fiscal year columns: At minimum, a Fiscal Year column that correctly identifies which fiscal year each date belongs to
Active relationships: Fact tables connected to the Date table with an active relationship
Data model understanding: Awareness of whether you have a single calendar or multiple (e.g., reporting vs operational)

If you don't have a Date table with fiscal year columns yet, I recommend reading my guide on How to Set a Custom Fiscal Year in Power BI first. It covers the complete Date table setup, including the fiscal year columns we'll reference here.

Step 1: Confirm Your Date Table is Marked as a Date Table

This is the foundation of all time intelligence in Power BI. If your Date table isn't explicitly marked, DAX time intelligence functions may not work correctly.

To mark your Date table:

In Power BI Desktop, go to the Data view
Click on your Date table
In the Table tools ribbon, click Mark as Date Table
Select your date column (typically Date[Date]) as the unique date identifier

Why this matters:

Marking a table as a Date table tells Power BI's engine that this table contains continuous, unique dates and can be used for time intelligence calculations. Without this flag, functions like TOTALYTD may not recognise your date column correctly.

Verify your Date table:

A proper Date table should have:

One row per day, with no gaps
A Date column with datatype Date (no time component)
Columns for Year, Month, Quarter, Weekday
Fiscal Year, Fiscal Month, Fiscal Quarter columns (for fiscal periods)

Step 2: Build Your Fiscal Year Columns

Fiscal YTD calculations rely on having the right fiscal year columns in your Date table. At minimum, you need:

Fiscal Year: Identifies which fiscal year each date belongs to (e.g., "FY 2025" for 1 Oct 2024–30 Sep 2025)
Fiscal Month Number: A numeric column (1–12) that correctly orders fiscal months

For an October–September fiscal year, your DAX would look like this:

-- Fiscal Year (October–September)
Fiscal Year =
    IF(
        MONTH('Date'[Date]) >= 10,
        YEAR('Date'[Date]) + 1,
        YEAR('Date'[Date])
    )

-- Fiscal Month Number (October = 1, November = 2, ...)
Fiscal Month Num =
    VAR CurrentMonth = MONTH('Date'[Date])
    RETURN
        IF(
            CurrentMonth >= 10,
            CurrentMonth - 9,
            CurrentMonth + 3
        )
    

For an April–March fiscal year (UK tax year), adjust the logic:

-- Fiscal Year (April–March, UK tax year)
Fiscal Year =
    IF(
        MONTH('Date'[Date]) >= 4,
        YEAR('Date'[Date]) + 1,
        YEAR('Date'[Date])
    )

-- Fiscal Month Number (April = 1, March = 12)
Fiscal Month Num =
    VAR CurrentMonth = MONTH('Date'[Date])
    RETURN
        IF(
            CurrentMonth >= 4,
            CurrentMonth - 3,
            CurrentMonth + 9
        )
    

Step 3: Correct TOTALYTD Syntax for Fiscal Years

The TOTALYTD function is the most common way to calculate year-to-date in Power BI. For calendar years, the syntax is simple:

Sales YTD =
    TOTALYTD(
        [Sales Amount],
        'Date'[Date]
    )
    

This works because TOTALYTD defaults to a 31 December year-end. For fiscal years, you must specify the year-end date as the third parameter:

-- Fiscal YTD for October–September year-end
Sales FY YTD =
    TOTALYTD(
        [Sales Amount],
        'Date'[Date],
        "30/09"  -- Fiscal year ends 30 September
    )

-- Fiscal YTD for April–March year-end (UK tax year)
Sales FY YTD =
    TOTALYTD(
        [Sales Amount],
        'Date'[Date],
        "31/03"  -- Fiscal year ends 31 March
    )
    

Pro tip: Use a date format DAX recognises.

The year-end parameter can be "30/09", "30-09", "September 30", or even DATE(2025, 9, 30). The day and year don't matter—only the month. I prefer "30/09" for clarity.

Understanding How TOTALYTD Works with Fiscal Year-Ends

When you provide a fiscal year-end date, TOTALYTD:

Identifies the current context's date
Finds the most recent fiscal year-end date before or on that date
Sums values from the day after that year-end up to the current context date

For example, with a 30 September year-end:

15 November 2025: YTD = 1 Oct 2025 to 15 Nov 2025
15 February 2025: YTD = 1 Oct 2024 to 15 Feb 2025
30 September 2025: YTD = full year (1 Oct 2024 to 30 Sep 2025)

Step 4: TOTALYTD vs DATESYTD vs Custom YTD Patterns

TOTALYTD isn't your only option for YTD calculations. Understanding the alternatives helps you choose the right approach for your scenario.

Function	Use Case	Fiscal Year Support	Performance
TOTALYTD	Simple YTD with optional fiscal year-end	Yes (via year-end parameter)	Fast
DATESYTD	Returns date range for YTD (not summed)	Yes (via year-end parameter)	Fast
CALCULATE + FILTER	Complex YTD with custom logic	Full flexibility	Slower on large models

DATESYTD: When to Use It

DATESYTD returns a table of dates from the start of the year to the current context date. It's useful when you need the date range rather than a pre-summed value:

Sales FY YTD (DATESYTD version) =
    CALCULATE(
        [Sales Amount],
        DATESYTD('Date'[Date], "30/09")
    )
    

Functionally, this is identical to TOTALYTD for simple YTD. The choice is stylistic—TOTALYTD is more readable for straightforward sums.

Custom YTD: When You Need Full Control

Sometimes you need YTD logic that goes beyond what TOTALYTD can handle—for example, excluding certain periods, handling partial years, or implementing complex business rules.

Sales FY YTD (Custom) =
    VAR FiscalYearEnd =
        CALCULATE(
            MAX('Date'[Date]),
            'Date'[Month] = "September",
            'Date'[Day] = 30,
            ALL('Date')
        )
    VAR CurrentDate = MAX('Date'[Date])
    VAR Result =
        CALCULATE(
            [Sales Amount],
            FILTER(
                ALL('Date'),
                'Date'[Date] > FiscalYearEnd &&
                'Date'[Date] <= CurrentDate
            )
        )
    RETURN
        Result
    

Use custom patterns sparingly.

Custom YTD calculations using FILTER can be significantly slower than TOTALYTD on large datasets. Only use them when you need logic that standard time intelligence can't handle.

Step 5: Handling Multiple Calendars (Reporting vs Operational)

Many organisations have two calendars: a reporting calendar (fiscal year for accounts) and an operational calendar (calendar year for operational reporting). Power BI handles this through role-playing dimensions.

Approach 1: Single Date Table with Multiple Fiscal Columns

The simplest approach is to add both fiscal year definitions to your Date table as separate columns:

-- Reporting fiscal year (October–September)
Reporting FY =
    IF(
        MONTH('Date'[Date]) >= 10,
        YEAR('Date'[Date]) + 1,
        YEAR('Date'[Date])
    )

-- Operational fiscal year (calendar year)
Operational FY =
    YEAR('Date'[Date])
    

Then create separate measures that reference the appropriate fiscal context:

Sales Reporting FY YTD =
    TOTALYTD([Sales Amount], 'Date'[Date], "30/09")

Sales Operational YTD =
    TOTALYTD([Sales Amount], 'Date'[Date])
    

Approach 2: Separate Date Tables with USERELATIONSHIP

For complex scenarios, you might create separate Date tables (e.g., Reporting Date and Operational Date) and use inactive relationships with USERELATIONSHIP:

Sales Reporting YTD =
    CALCULATE(
        TOTALYTD([Sales Amount], 'Reporting Date'[Date], "30/09"),
        USERELATIONSHIP(Sales[Reporting Date], 'Reporting Date'[Date])
    )
    

Recommendation: Start with Approach 1.

Multiple Date tables add complexity to your model. Only use separate Date tables when you genuinely have different date fields (e.g., Order Date vs Ship Date). For different interpretations of the same date, additional columns on a single Date table are simpler and more maintainable.

Step 6: Production-Ready Fiscal YTD Measures

Here's a complete set of fiscal YTD measures you can adapt for your model. These cover the typical requirements for management accounts and board packs.

Base YTD Measure

Sales FY YTD

TOTALYTD(
    [Sales Amount],
    'Date'[Date],
    "30/09"
)
        

Prior Year YTD

Sales PY FY YTD

CALCULATE(
    [Sales FY YTD],
    SAMEPERIODLASTYEAR(
        'Date'[Date]
    )
)
        

Variance (£)

Sales FY YTD Var £

[Sales FY YTD] -
[Sales PY FY YTD]
        

Variance (%)

Sales FY YTD Var %

DIVIDE(
    [Sales FY YTD Var £],
    [Sales PY FY YTD]
)
        

Full Year Comparison

Sales FY Full

CALCULATE(
    [Sales Amount],
    ALL('Date'),
    'Date'[Fiscal Year] =
        MAX('Date'[Fiscal Year])
)
        

YTD vs Full Year %

Sales FY YTD % of Full

DIVIDE(
    [Sales FY YTD],
    [Sales FY Full]
)
        

Rolling 12 Months vs Fiscal YTD

Some finance teams prefer rolling 12-month comparisons alongside fiscal YTD. Here's how to implement both:

-- Fiscal Year-to-Date (e.g., Oct to current date)
Sales FY YTD =
    TOTALYTD(
        [Sales Amount],
        'Date'[Date],
        "30/09"
    )

-- Rolling 12 Months (previous 12 months from current date)
Sales R12M =
    CALCULATE(
        [Sales Amount],
        DATESINPERIOD(
            'Date'[Date],
            MAX('Date'[Date]),
            -12,
            MONTH
        )
    )
    

Which should you use?

Fiscal YTD compares current progress against the same point last fiscal year (e.g., "We're 6 months into FY25, how does this compare to 6 months into FY24?"). Rolling 12 months smooths seasonality and shows the trend over the last year. Most finance teams use both for different purposes.

Step 7: Testing and Validation Checks

Before deploying fiscal YTD measures to production, validate them thoroughly. Here's a testing checklist:

Validate fiscal year boundaries: Check the first and last months of your fiscal year. For an October–September year, October YTD should equal October's actuals. September YTD should equal the full fiscal year total.
Compare against Excel: Export your raw data to Excel and manually calculate YTD for a few months. Power BI should match exactly.
Test across fiscal years: Create a matrix visual with Fiscal Year on rows and Fiscal Month on columns. Each cell should show the correct cumulative total.
Check prior year calculations: SAMEPERIODLASTYEAR should return the equivalent fiscal period from last year, not the calendar equivalent.
Test at different granularity: Validate YTD works correctly at day, week, month, quarter, and year levels.
Verify with finance stakeholders: Share your Power BI file with the finance team and ask them to spot-check against their existing reports.

Validation Query Pattern

Use DAX Studio or a Table visual to create a validation table that shows both the underlying data and your YTD calculation side by side:

YTD Validation =
VAR MonthlySales = [Sales Amount]
VAR YTDSales = [Sales FY YTD]
VAR RunningTotal =
    CALCULATE(
        [Sales Amount],
        FILTER(
            ALL('Date'),
            'Date'[Date] <= MAX('Date'[Date])
        )
    )
RETURN
    ADDCOLUMNS(
        SUMMARIZE(
            'Date',
            'Date'[Fiscal Year],
            'Date'[Fiscal Month],
            "Monthly Sales", MonthlySales,
            "Running Total", RunningTotal,
            "YTD Measure", YTDSales
        )
    )
    

Critical check: Verify year-end dates.

The most common fiscal YTD error is an incorrect year-end date. Double-check that your TOTALYTD parameter ("30/09", "31/03", etc.) matches your actual fiscal year-end.

Common Mistakes: 8 Ways Fiscal YTD Goes Wrong

Based on real client work, here are the most common mistakes I see when implementing fiscal year-to-date calculations.

Forgetting the year-end parameter: Using TOTALYTD([Sales], 'Date'[Date]) without a fiscal year-end date. This defaults to calendar year YTD, giving wrong figures for any non-calendar fiscal year.

Impact: YTD calculated from 1 January instead of your fiscal year start.
Wrong year-end date format: Using an invalid date format like "September 30, 2025" or "09-30-2025". Power BI expects specific formats ("30/09", "30-09", or DATE function).

Impact: TOTALYTD returns an error or falls back to calendar year.
Marking the wrong table as Date table: Marking a fact table or a non-contiguous date table as the Date table. This breaks time intelligence.

Impact: Unpredictable YTD results, often silently wrong.
Gaps in the Date table: The Date table is missing dates (e.g., starts after your first transaction or has gaps). Time intelligence assumes continuous dates.

Impact: YTD stops at the last date in your Date table, missing recent transactions.
Using SAMEPERIODLASTYEAR without fiscal context: SAMEPERIODLASTYEAR works on calendar years by default. For fiscal years, it may return unexpected dates if your Date table isn't set up correctly.

Impact: Prior year comparisons show the wrong period.
Confusing fiscal year with calendar year in visuals: Using calendar year slicers with fiscal YTD measures (or vice versa), creating misalignment between what the user selects and what the measure calculates.

Impact: User sees YTD for a different period than they selected.
Mixing fiscal and calendar measures: Having both fiscal YTD and calendar YTD measures in the same report without clear labelling, leading to confusion about which is which.

Impact: Stakeholders use the wrong measure, making decisions on incorrect data.
Not validating against source systems: Trusting Power BI's output without reconciling against the ERP or accounting system's built-in YTD calculations.

Impact: Undetected errors propagate to board reports and decision-making.

Frequently Asked Questions

Why is my Power BI YTD starting from January instead of April/October?

Your TOTALYTD or DATESYTD function is missing the fiscal year-end parameter. Add it as the third argument: TOTALYTD([Measure], 'Date'[Date], "31/03") for April year-end or "30/09" for September year-end.

What's the difference between TOTALYTD and DATESYTD?

TOTALYTD returns a summed value (e.g., total sales from year start to current date). DATESYTD returns a table of dates, which you then use with CALCULATE to sum values. Functionally, TOTALYTD([Sales], 'Date'[Date]) is equivalent to CALCULATE([Sales], DATESYTD('Date'[Date])). TOTALYTD is more readable for simple sums.

How do I calculate fiscal YTD for a February year-end?

Use TOTALYTD with "28/02" (or "29/02" for leap years) as the year-end parameter: TOTALYTD([Sales], 'Date'[Date], "28/02"). Power BI will calculate YTD from 1 March to the current date in your fiscal year.

Why does SAMEPERIODLASTYEAR give wrong dates for fiscal years?

SAMEPERIODLASTYEAR shifts dates by exactly 365 days (or 366 in leap years). If your fiscal year doesn't align with the calendar year, this can shift you into a different fiscal period. For fiscal comparisons, use custom logic or ensure your Date table correctly identifies fiscal periods.

Can I have both fiscal YTD and calendar YTD in the same report?

Yes. Create separate measures: Sales Fiscal YTD with a year-end parameter and Sales Calendar YTD without. Clearly label visuals and use tooltips to help users understand which measure they're viewing.

How do I show YTD progress against target or budget?

Create separate measures for actual YTD and budget YTD (using the same TOTALYTD pattern), then calculate variance: Variance £ = [Actual YTD] - [Budget YTD] and Variance % = DIVIDE([Variance £], [Budget YTD]).

What if my fiscal year starts on a different day each year (e.g., 52/53-week year)?

Standard TOTALYTD won't work for 52/53-week fiscal years. You'll need custom logic using CALCUATETABLE and FILTER to define YTD based on your specific fiscal week pattern. This is more complex and typically requires a custom ISO week column in your Date table.

How can I debug YTD that's returning unexpected values?

Create a simple table visual with Date, Monthly Sales, and your YTD measure side by side. Check if YTD is cumulative as expected. Use DAX Studio to inspect the date range being returned by DATESYTD. Verify your Date table is marked correctly and has no gaps.

Do I need a separate Date table for each fiscal year definition?

Usually not. Add multiple fiscal year columns to a single Date table (e.g., Fiscal Year Oct-Sept, Fiscal Year Apr-Mar) and create measures referencing the appropriate column or year-end parameter. Multiple Date tables add unnecessary complexity in most cases.

Why does my YTD show blank for certain dates?

Check for: (1) Gaps in your Date table—ensure it covers all dates in your data range, (2) Incorrect relationships—fact tables must relate to the Date table, (3) The year-end parameter in TOTALYTD may be creating a date range with no data.

Need Help Implementing Fiscal YTD Correctly?

Fiscal year-to-date calculations seem straightforward, but getting them right—and keeping them right as your model evolves—requires attention to detail. I've seen too many organisations make decisions on incorrect YTD figures because of a missing year-end parameter or misconfigured Date table.

If you're unsure whether your Power BI model's YTD is correct, or if you need help implementing fiscal year calculations for a complex reporting structure, let's talk.

Power BI Audit

I'll review your existing Power BI model, identify any issues with time intelligence or fiscal calculations, and provide prioritised recommendations to fix them.

Book a Power BI Audit from £299

Free Strategy Call

Not sure where to start? Let's discuss your Power BI challenges and explore whether fiscal YTD is the tip of the iceberg or a quick fix.

Book a Free 30-Minute Call

Related Guides

Fiscal YTD is one piece of the Power BI time intelligence puzzle. These related guides cover the foundations you need to get everything working correctly:

How to Set a Custom Fiscal Year (October–September) in Power BI — Build the Date table and fiscal year columns that fiscal YTD depends on.
How to Sort Months Correctly in Power BI (Including Fiscal Years) — Fix alphabetical month sorting so your fiscal months appear in the right order.

Related Services

Digital Adaption provides comprehensive Power BI consultancy services including data modelling, dashboard development, and team training for UK organisations.

If your Power BI reports with YTD calculations are running slowly, 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.

Last updated: 30 December 2025 | Author: Matty, Senior Business Analyst at Digital Adaption

Back to Digital Adaption Home