DAX year-to-date versus rolling twelve months

Year-to-date accumulates from the start of the relevant year to the selected endpoint. A rolling twelve-month measure looks backward over a moving twelve-month interval. They answer different questions and usually include different records.

Define the as-of date, year convention and boundary policy before choosing a function. A formula cannot compensate for missing historical source coverage.

Use the dedicated daily fixture

For this lesson, import FactDailyValue.csv from the retail lab's separate time exercises. Do not use FactSales for the time-series totals below.

FactDailyValue contains a complete synthetic daily series from January 1, 2025 through February 28, 2026: 1,000 paise per day in 2025 and 2,000 per supplied day in 2026. Relate its Date column to the complete DimDate table, using Date types and a single-direction relationship.

Create Daily Net Paise = SUM(FactDailyValue[NetPaise]). Configure DimDate as the date table for the classic date-column time-intelligence examples used here.

Calculate calendar YTD

Daily Net YTD =
CALCULATE([Daily Net Paise], DATESYTD(DimDate[Date]))

At February 28, 2026, the window is January 1 through February 28: 59 days at 2,000 paise, totaling 118,000. The year resets on January 1 under this calendar-year definition.

Microsoft documents the date-column and calendar-reference forms of DATESYTD in its function reference. This example uses the date-column form and an ordinary calendar year.

Calculate the trailing twelve-month interval

Daily Net Rolling 12 Months =
CALCULATE(
    [Daily Net Paise],
    DATESINPERIOD(DimDate[Date], MAX(DimDate[Date]), -12, MONTH)
)

With an endpoint of February 28, 2026, the intended date-column interval covers March 1, 2025 through February 28, 2026. It includes 306 days at 1,000 plus 59 days at 2,000, totaling 424,000 paise.

The rolling result includes ten months from the previous calendar year, while YTD does not. Its larger value is therefore expected, not evidence that one measure is wrong. See DATESINPERIOD documentation for interval semantics and supported forms.

Control the endpoint explicitly

The formula uses the maximum date in the current context. If the unfiltered date table extends to December 2026, that is not the same as the latest complete source date in February.

Use a report selection or an explicit, validated as-of policy. Display the endpoint so readers know which window they are viewing. Do not assume MAX(calendar date) means “latest loaded data.”

At a mid-month endpoint, a rolling twelve-month interval is not necessarily twelve complete calendar months. If the report requires the last twelve completed months, derive a completed-month endpoint under the source coverage policy and label that measure separately.

Verify historical coverage

The dedicated fixture deliberately supplies the full trailing window for this example. The original FactSales table supplies only a small January–February transaction scenario; using it would not establish a valid year-long business history.

Check source completeness independently of calendar completeness. A date table can contain every expected date while fact data is missing for several months. A rolling SUM over that incomplete population can still return a plausible number.

For incomplete windows, show an unavailable or partial status according to the report's contract rather than labelling the result a complete trailing-year total.

Test the year boundary

At January 1, calendar YTD contains one day. The rolling window includes the preceding months plus that day, subject to the exact boundary convention. Test December 31, January 1 and a month-end explicitly.

For fiscal YTD, use the correct fiscal calendar or supported year-end configuration and document the fiscal-year label. Do not assume a January-start formula answers an April-start business year.

Python verifies the fixture's 118,000 and 424,000 controls in time-example-results.json. DAX execution and date-table configuration still require Power BI review.

Exercise: compare the measures at January 31 and February 28, 2026. Explain which dates enter and leave each window, then design a visible warning when the selected endpoint exceeds the source's complete coverage.

NeuraPath's Data Analytics with Generative AI course connects DAX time functions with reporting-period design. A reliable time measure states its window and proves that the required history exists.

Continue learning

This article is part of the DAX measures and analytical correctness sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Build a distinct-customer measure with clear eligibility rules.
• Continue with Compare same-period sales when calendars differ.