Power BI fiscal year YTD in DAX: TOTALYTD with a June 30 year end
A Power BI fiscal year-to-date measure for a company whose year ends on June 30 needs one extra argument: TOTALYTD accepts an optional year_end_date string, and Microsoft's DAX reference uses "6/30" for exactly this case. Leave it out and the function falls back to December 31, so a report filtered to October 2026 adds up January to October 2026 instead of July to October 2026. Both numbers look plausible on a card, and only one is the fiscal figure.
The prior-year comparison has its own trap. A SAMEPERIODLASTYEAR measure placed on a card without a month filter shifts the whole date table back one year, and if the date table runs to the end of the current fiscal year, the comparison lands on the complete prior year rather than on the same four months. In the illustrative dataset used below, that turns a real growth rate of 7.7% into an apparent drop of 64.8%, an error that survives until someone checks the measure against a hand calculation.
Below are the date table, the fiscal YTD and prior-year measures, a check query Copilot in Power BI can write or explain, and the expected results computed in Python. Function behavior and requirements were checked against Microsoft Learn on November 7, 2026.
··········
POWER BI FISCAL YEAR YTD: WHAT THE MEASURES HAVE TO RETURN.
Fiscal reporting usually asks for sales for the period, sales accumulated since the start of the fiscal year, the same accumulation for the prior fiscal year up to the same point, and the growth between the last two. For a July to June fiscal year, the report for October 2026 must therefore compare July to October 2026 (fiscal 2027) with July to October 2025 (fiscal 2026).
Microsoft documents two ways to build these calculations in Power BI. Classic time intelligence uses a dedicated date table and functions such as TOTALYTD, DATESYTD, SAMEPERIODLASTYEAR and DATEADD with a date column. Calendar-based time intelligence, which Microsoft Learn labels as a preview, lets you define calendars as metadata on a table and pass the calendar name to the same functions. Microsoft recommends the calendar-based approach for performance and flexibility, while the classic approach needs no preview features and handles a shifted Gregorian fiscal year such as July to June without extra setup.
........
Option | Status on Microsoft Learn | How the fiscal year end is set | Main documented constraint |
Classic time intelligence | Standard, no preview label | year_end_date string, for example "6/30" | Needs a date table with contiguous dates |
Calendar-based time intelligence | Preview | Calendar defined on the date table | Not for live connected or composite models |
Copilot in DAX query view | Requires Fabric capacity | Only through the measures it is given | Uses model metadata, may not know recent functions |
........
For the classic route, the Power BI documentation on date tables lists the validations Power BI Desktop runs when you mark a table as a date table: unique values, no nulls, contiguous dates from beginning to end, and the same timestamp on every value for a Date/Time column. The DATEADD reference adds that the function returns an error when the dates in the current context do not form a contiguous interval, so the calendar is best built with a generator function rather than derived from the fact table.
··········
BUILDING A FISCAL DATE TABLE IN POWER BI WITH DAX.
The date table below covers two complete fiscal years, from July 1, 2025 to June 30, 2027, and adds a fiscal year label, a fiscal month number for sorting, and a month label. Covering whole fiscal years keeps the calendar contiguous and avoids blank fiscal months at the edges of the model.
Date =
ADDCOLUMNS (
CALENDAR ( DATE ( 2025, 7, 1 ), DATE ( 2027, 6, 30 ) ),
"Fiscal Year", "FY" & ( YEAR ( [Date] ) + IF ( MONTH ( [Date] ) >= 7, 1, 0 ) ),
"Fiscal Month Number", MONTH ( [Date] ) + IF ( MONTH ( [Date] ) >= 7, -6, 6 ),
"Month", FORMAT ( [Date], "mmm yyyy" )
)After creating the table, mark it as the date table on the Date column and relate 'Date'[Date] to the order date column of the fact table. Marking the table also removes the hidden tables that auto date/time creates for that column, according to the Power BI documentation, so visuals and measures use one consistent calendar.
One more calculated column records whether a date falls on or before the last order in the fact table. It keeps prior-year comparisons correct on cards and totals, which the worked example below demonstrates.
HasSales = 'Date'[Date] <= MAX ( Sales[OrderDate] )In a calculated column, MAX without CALCULATE scans the whole Sales table, so every row of the date table is compared with the same latest order date.
··········
TOTALYTD AND SAMEPERIODLASTYEAR MEASURES FOR A JUNE 30 YEAR END.
The base measure and the fiscal YTD measure follow the TOTALYTD syntax documented on Microsoft Learn, TOTALYTD(expression, dates, filter, year_end_date), where the filter argument is optional. The documentation states that the year portion of year_end_date is ignored, that the string is read in the locale of the client where the file was created, and it recommends the month/day form to make sure the string resolves to a date. The DATESYTD reference shows "6-30" as an equivalent US-locale example.
Sales = SUM ( Sales[Amount] )
Sales FYTD = TOTALYTD ( [Sales], 'Date'[Date], "6/30" )
Sales FYTD PY =
CALCULATE (
[Sales FYTD],
CALCULATETABLE ( SAMEPERIODLASTYEAR ( 'Date'[Date] ), 'Date'[HasSales] = TRUE () )
)
FYTD Growth % = DIVIDE ( [Sales FYTD] - [Sales FYTD PY], [Sales FYTD PY] )SAMEPERIODLASTYEAR returns the dates of the current context shifted one year back, and Microsoft documents it as equivalent to DATEADD(dates, -1, year). Both keep only dates that exist in the date column, so fiscal 2026 months return a blank prior-year value here because the calendar starts on July 1, 2025. Wrapping the shift in CALCULATETABLE with the HasSales filter limits the dates to those up to the last order before they are moved back a year.
The reference pages for all four functions carry the same two warnings: they are not supported in DirectQuery mode when used in calculated columns or row-level security rules, and they are discouraged in visual calculations, where they are likely to return meaningless results. Model measures, as above, stay inside the documented use.
··········
WORKED EXAMPLE: FISCAL YTD AND PRIOR-YEAR YTD CHECKED IN PYTHON.
The dataset is illustrative: one sales total per month from July 2025 to October 2026, in USD, with the last order dated October 31, 2026. Monthly totals are enough to verify the measures because every month is either fully inside or fully outside a year-to-date window.
........
Month | Fiscal year | Sales (USD) | Month | Fiscal year | Sales (USD) |
Jul 2025 | FY2026 | 182,400 | Mar 2026 | FY2026 | 184,600 |
Aug 2025 | FY2026 | 175,900 | Apr 2026 | FY2026 | 179,300 |
Sep 2025 | FY2026 | 190,300 | May 2026 | FY2026 | 192,800 |
Oct 2025 | FY2026 | 201,700 | Jun 2026 | FY2026 | 205,400 |
Nov 2025 | FY2026 | 214,500 | Jul 2026 | FY2027 | 196,500 |
Dec 2025 | FY2026 | 238,100 | Aug 2026 | FY2027 | 188,200 |
Jan 2026 | FY2026 | 168,200 | Sep 2026 | FY2027 | 207,900 |
Feb 2026 | FY2026 | 159,800 | Oct 2026 | FY2027 | 215,600 |
........
The expected results were computed in pandas by assigning each month to its fiscal year, accumulating sales within the fiscal year, and joining each fiscal month to the same fiscal month of the year before. The code below is the calculation used for the tables in this section.
import pandas as pd
sales = [182400, 175900, 190300, 201700, 214500, 238100, 168200, 159800,
184600, 179300, 192800, 205400, 196500, 188200, 207900, 215600]
df = pd.DataFrame({"month": pd.period_range("2025-07", "2026-10", freq="M"), "sales": sales})
df["fy"] = df.month.dt.year + (df.month.dt.month >= 7)
df["fm"] = (df.month.dt.month - 7) % 12 + 1
df["fytd"] = df.groupby("fy").sales.cumsum()
py = df[["fy", "fm", "fytd"]].assign(fy=lambda d: d.fy + 1).rename(columns={"fytd": "py"})
df = df.merge(py, on=["fy", "fm"], how="left")
df["growth"] = (df.fytd - df.py) / df.py
print(df[df.fy == 2027])For fiscal 2027, the correct values by month are the ones below. These are the numbers the Power BI measures must reproduce in a table visual with fiscal year and fiscal month on the rows.
........
Fiscal month | Sales | Sales FYTD | Sales FYTD PY | FYTD Growth % |
Jul 2026 (FM 1) | 196,500 | 196,500 | 182,400 | 7.7% |
Aug 2026 (FM 2) | 188,200 | 384,700 | 358,300 | 7.4% |
Sep 2026 (FM 3) | 207,900 | 592,600 | 548,600 | 8.0% |
Oct 2026 (FM 4) | 215,600 | 808,200 | 750,300 | 7.7% |
........
The October 2026 row is the headline figure: fiscal YTD sales of 808,200 against 750,300 a year earlier, a difference of 57,900 and growth of 7.7%. Two wrong versions are easy to produce and both should be tested on purpose before the report is published.
........
Measure variant | Result for Oct 2026 or card total | Why it differs |
TOTALYTD without "6/30" | 1,898,300 | Adds Jan to Oct 2026 across two fiscal years |
Prior-year YTD without HasSales filter (card) | 2,293,000 | Compares with the full fiscal 2026 |
Growth on that card | -64.8% | 808,200 against a 12-month total |
Correct FYTD Growth % | 7.7% | Jul to Oct 2026 against Jul to Oct 2025 |
........
The card case follows from how the measures evaluate. With no date filter, the current-year TOTALYTD runs to June 30, 2027, the last date in the calendar, which still yields 808,200 because no later sales exist. A plain SAMEPERIODLASTYEAR on the same unfiltered context shifts the whole calendar back, so its year-to-date ends on June 30, 2026 and picks up all twelve months of fiscal 2026. The HasSales filter cuts the calendar at October 31, 2026 before the shift, and the prior-year window ends on October 31, 2025.
··········
USING COPILOT IN POWER BI TO WRITE AND EXPLAIN THE CHECK QUERY.
Copilot can produce the verification query in DAX query view, where Microsoft documents three tasks: generating a DAX query from natural language, explaining a DAX query, and explaining DAX concepts. In Power BI Desktop, the documentation says to enable DAX query view with Copilot under Preview features in Options, then open Copilot from the query tab with the Copilot button or Ctrl+I. In the Power BI service, the Write DAX queries option on a published semantic model opens the same view.
A prompt along these lines asks for a query that can be run and compared with the expected table above.
Write a DAX query that returns Sales, Sales FYTD, Sales FYTD PY and FYTD Growth %
by 'Date'[Fiscal Year] and 'Date'[Fiscal Month Number], only for dates where
'Date'[HasSales] is TRUE, ordered by fiscal year and fiscal month number.The query you should end up with, whether Copilot writes it or you correct its draft, looks like this.
EVALUATE
SUMMARIZECOLUMNS (
'Date'[Fiscal Year],
'Date'[Fiscal Month Number],
TREATAS ( { TRUE () }, 'Date'[HasSales] ),
"Sales", [Sales],
"Sales FYTD", [Sales FYTD],
"Sales FYTD PY", [Sales FYTD PY],
"FYTD Growth %", [FYTD Growth %]
)
ORDER BY 'Date'[Fiscal Year], 'Date'[Fiscal Month Number]Run the query, then compare the fiscal 2027 rows with the expected values: 808,200, 750,300 and 7.7% for fiscal month 4. The fiscal 2026 rows should show sales and a running FYTD reaching 2,293,000 in fiscal month 12, with a blank prior-year column. If a row differs, ask Copilot to explain the query and the measures it references, and check how the filter and the year-end argument are read. The same review applies to the variance measures of a Power BI budget vs actual dashboard with Copilot and DAX, which usually share the fiscal date table.
··········
COPILOT REQUIREMENTS AND LIMITS FOR DAX IN POWER BI.
Microsoft's Copilot enablement page states that the workspace must be on Power BI Premium capacity (P1 and above), a paid Fabric capacity (F2 and above) or a Fabric Copilot capacity, and that trial capacities are not supported. In Power BI Desktop without a Fabric Copilot capacity, a user needs admin, member or contributor access to at least one workspace on a paid capacity with Copilot enabled. How these capacities compare in cost with per-user licensing is covered in our breakdown of Power BI Pro vs Premium Per User vs Fabric F64.
Copilot for Fabric is enabled by default in the admin portal and can be disabled by administrators. The capacity must be in a supported Fabric region; where Azure OpenAI is not available in the capacity's geography, an administrator must allow cross-region processing, and Copilot is not currently supported for Private Link or closed network environments.
The documented limits bear directly on fiscal YTD work:
........
Documented Copilot limit | Effect on fiscal YTD measures |
Uses model metadata only | Cannot know your fiscal year end unless names or descriptions state it |
May use min/max for sample values in import mode | Does not read the sales rows to test a result |
Recent DAX functions may be unknown | Calendar-based syntax may be explained incorrectly |
Descriptions read up to 200 characters | Put "fiscal year ends June 30" early in the description |
Syntax checked and retried once | A query that parses can still return the calendar YTD |
........
Adding descriptions to the Date table and to the measures is the most useful preparation. A description such as "Fiscal year ends June 30; FYTD runs July 1 to the last date in context" gives Copilot the year-end fact that the column names do not carry, and it helps anyone who opens the model later.
··········
CALENDAR-BASED TIME INTELLIGENCE AS THE NEXT STEP.
The preview calendar feature changes how the year end is expressed. After enabling Enhanced DAX Time Intelligence in Power BI Desktop's preview features and restarting, you define a calendar on the date table through Calendar options and assign columns to categories such as Year, Quarter, Month and Date. A fiscal calendar built on the Fiscal Year column then drives the functions directly, and the TOTALYTD reference states that year_end_date must not be specified when a calendar is used.
Sales FYTD (calendar) = TOTALYTD ( [Sales], 'Fiscal Calendar' )Microsoft lists considerations for production models: calendars cannot yet be authored in the Power BI service, auto date/time tables should not be used with custom calendars, calendars do not work with live connected or composite models, and calendar-based functions cannot be nested. The DATEADD reference also documents a difference at leap years: shifting February 29, 2008 back one year returns March 1, 2007 with a calendar and February 28, 2007 with classic time intelligence. Any switch from classic measures to calendar measures should therefore be tested against the same expected table, month by month.
Teams that expose these measures outside Power BI should keep the definitions in one place. Databricks, for example, now lets Genie reuse Power BI measure definitions, as described in our report on Databricks Genie and Power BI DAX measures, and a fiscal YTD measure with a missing year-end argument would be reused with the same error.
··········
POWER BI FISCAL YTD MEASURES: WHAT TO VERIFY BEFORE YEAR-END REPORTING.
Microsoft's documentation confirms the mechanics: TOTALYTD and DATESYTD accept a year_end_date string that defaults to December 31, SAMEPERIODLASTYEAR equals a one-year DATEADD shift and keeps only dates present in the date column, and the classic functions need a contiguous, marked date table.
Copilot depends on your environment: a paid Premium or Fabric capacity, tenant and regional settings that allow it, and model descriptions that state the fiscal year end, since it reads metadata and not the rows that would expose a wrong window. Calendar-based time intelligence remains a preview with documented limits.
The practical test is short. For the last complete month, compute fiscal YTD, prior-year fiscal YTD and growth outside Power BI, then confirm that the measures return the same values both in a monthly table and on a card with no date filter. In the illustrative data that means 808,200, 750,300 and 7.7% in both places, and any result near 1,898,300 or -64.8% points to a missing year-end argument or an uncapped prior-year window.
··········
FOLLOW US FOR MORE.
·····
DATA STUDIOS
·····
datastudios.org
