Time intelligence for NHS financial years and rolling periods

Time intelligence functions in DAX are CALCULATE with a ready-made date filter. The functions themselves are rarely the problem. The problems come from the date table, from a financial year that starts in April, and from the current month being only partly loaded when someone opens the report.

The examples use a synthetic admissions table for an invented acute hospital, with data from 1 April 2023 to Thursday 20 August 2026 and a date table that runs to 31 March 2027. Every number here comes from the seeded generator in projects/article-examples/power-bi/. The base measure is:

Admissions := COUNTROWS ( Admissions )

A date table you can mark

Classic time intelligence needs a date table: one row per day, no gaps, covering whole financial years. Here’s a DAX version for NHS years:

Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2023, 4, 1 ), DATE ( 2027, 3, 31 ) ),
    "Financial Year",
        VAR StartYear = YEAR ( [Date] ) - IF ( MONTH ( [Date] ) < 4, 1, 0 )
        RETURN StartYear & "/" & RIGHT ( FORMAT ( StartYear + 1, "0" ), 2 ),
    "FY Month Number", MOD ( MONTH ( [Date] ) - 4, 12 ) + 1,
    "Month Start", EOMONTH ( [Date], -1 ) + 1,
    "Month", FORMAT ( [Date], "mmm yyyy" ),
    "Week Start", [Date] - WEEKDAY ( [Date], 2 ) + 1,
    "Weekday", FORMAT ( [Date], "dddd" )
)

FY Month Number puts April first, for sorting any month-of-year column. Sort Month by Month Start so it runs in date order rather than alphabetically.

Then mark it: select the table, choose Mark as date table and pick the Date column. Power BI Desktop checks that the column has unique values, no blanks and no gaps. Marking matters for one behaviour in particular: when CALCULATE filters 'Date'[Date], DAX removes the filters on every other column of the Date table. Without that, a visual grouped by 'Date'[Month] would keep its month filter while SAMEPERIODLASTYEAR asks for last year’s dates, and the two would intersect to nothing. (The same automatic removal happens when the relationship uses a column of Date type, which is one more reason to relate on real dates, as the star schema article recommends.)

Finally, turn off Auto date/time in the options. It creates a hidden date table for every date column, which you don’t need once you have your own and which adds to model size.

Financial year to date

DATESYTD takes an optional year-end date. Leave it out and the year ends on 31 December:

-- Wrong for NHS reporting: resets every January
Admissions YTD := CALCULATE ( [Admissions], DATESYTD ( 'Date'[Date] ) )

-- Right: financial year ending 31 March
Admissions FYTD := CALCULATE ( [Admissions], DATESYTD ( 'Date'[Date], "2026-03-31" ) )

The year part of that string is ignored. Microsoft’s documentation says the string is read in the locale of the client where the model was created, which leaves “3/31” versus “31/3” open to interpretation; I write it as yyyy-mm-dd, following SQLBI’s advice, so there’s nothing to misread.

The calendar-year version says April 2026 had 18,706 admissions to date, because it counts from January. The financial-year version starts again on 1 April.

Blank future dates

Look at September and October in that table. The date table runs to March 2027 so that financial years are complete, and DATESYTD happily returns 1 April to 30 September for a month that hasn’t happened. The measure repeats the last value, and a line chart draws a flat line into the future.

Guard it with the last date that has data:

Last Data Date := CALCULATE ( MAX ( Admissions[Admission Date] ), REMOVEFILTERS () )

Admissions FYTD :=
VAR LastDate = [Last Data Date]
RETURN
    IF (
        MIN ( 'Date'[Date] ) <= LastDate,
        CALCULATE ( [Admissions], DATESYTD ( 'Date'[Date], "2026-03-31" ) )
    )

IF without a third argument returns BLANK, and visuals skip blank points. REMOVEFILTERS () with no arguments clears every filter, so Last Data Date is the same in every cell.

Incomplete current periods

August 2026 is still being loaded: 20 days of data. Compare it with the whole of August 2025 and it looks like a collapse.

Data table

Month	Admissions	Same month last year
2025-04-01	4,392	4,294
2025-05-01	4,250	4,138
2025-06-01	4,009	3,976
2025-07-01	4,246	4,059
2025-08-01	4,181	4,020
2025-09-01	4,365	4,148
2025-10-01	4,666	4,533
2025-11-01	4,588	4,374
2025-12-01	5,039	4,727
2026-01-01	5,059	4,957
2026-02-01	4,467	4,382
2026-03-01	4,718	4,552
2026-04-01	4,462	4,392
2026-05-01	4,455	4,250
2026-06-01	4,263	4,009
2026-07-01	4,312	4,246
2026-08-01	2,738	4,181

Synthetic data. Source: projects/article-examples/power-bi.

-- Misleading for the current month: compares 20 days with 31
Admissions PY := CALCULATE ( [Admissions], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

-- Like for like: only shift the dates that have data this year
Admissions PY :=
VAR LastDate = [Last Data Date]
VAR DatesWithData = FILTER ( VALUES ( 'Date'[Date] ), 'Date'[Date] <= LastDate )
RETURN
    IF (
        MIN ( 'Date'[Date] ) <= LastDate,
        CALCULATE ( [Admissions], SAMEPERIODLASTYEAR ( DatesWithData ) )
    )

Admissions YoY % := DIVIDE ( [Admissions] - [Admissions PY], [Admissions PY] )

A 34.5% fall becomes a 0.3% rise. Note that LastDate is read into a variable before FILTER starts iterating. Referencing the measure inside FILTER would evaluate it once per date, each time with a context transition (the CALCULATE article explains why). This measure clears all filters, so the answer wouldn’t change, but it’s wasted work, and the habit gives wrong answers with measures that do respond to filters.

Rolling 12 months

DATESINPERIOD returns a window that ends on a given date. With the last visible date as the end, you get a rolling total:

Admissions R12M :=
VAR LastDate = [Last Data Date]
RETURN
    IF (
        MAX ( 'Date'[Date] ) <= LastDate,
        CALCULATE (
            [Admissions],
            DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -12, MONTH )
        )
    )

For August, the window runs from 1 September 2025 to 31 August 2026, but only 20 of those August days have data, so the “12-month” total is really eleven and two-thirds months, and it dips. September is worse. The guard here tests MAX, not MIN: a rolling total that includes a partial month isn’t a 12-month total, so the line stops at July until August is complete. FYTD uses MIN because a to-date figure for the current month is still meaningful. Pick the guard that matches what the number claims to be.

Week-based comparisons

SAMEPERIODLASTYEAR shifts dates by a calendar year, so Monday 17 August 2026 is compared with Sunday 17 August 2025. For monthly and yearly totals that’s usually acceptable, because both periods cover the same whole months. For daily or week-to-date views of anything with a weekly cycle, and admissions have a strong one, it matters a lot.

Data table

Date (August 2026)	Admissions	Same date last year	364 days earlier
2026-08-01	94	160	117
2026-08-02	104	117	93
2026-08-03	148	93	155
2026-08-04	163	155	146
2026-08-05	144	146	154
2026-08-06	132	154	137
2026-08-07	141	137	142
2026-08-08	110	142	118
2026-08-09	134	118	95
2026-08-10	145	95	144
2026-08-11	155	144	163
2026-08-12	166	163	150
2026-08-13	145	150	146
2026-08-14	139	146	146
2026-08-15	115	146	121
2026-08-16	99	121	104
2026-08-17	150	104	158
2026-08-18	148	158	140
2026-08-19	151	140	142
2026-08-20	155	142	155

Synthetic data. Source: projects/article-examples/power-bi.

Shifting by 364 days, exactly 52 weeks, keeps weekdays aligned:

Admissions Same Weekday LY :=
CALCULATE ( [Admissions], DATEADD ( 'Date'[Date], -364, DAY ) )

The calendar shift swaps a Thursday for a Sunday and reports an 11% rise; the weekday-aligned shift reports 1.5%. The 364-day window drifts by a day a year against the calendar (two across a 29 February), so I use it for daily and weekly views and keep SAMEPERIODLASTYEAR for months and financial years.

Power BI also has calendar-based time intelligence, where you tag the columns of your date table as years, months, weeks and so on, and functions such as TOTALWTD work at week level. At the time of writing it’s a preview feature (Enhanced DAX Time Intelligence in the preview options), so I’d try it in a copy of the model before relying on it.

Calculation groups instead of measure explosion

Five base measures (admissions, discharges, bed days, outpatient attendances, DNAs) with six time variants each is thirty measures to write, test and keep consistent. A calculation group holds the variants once and applies them to whichever measure is in the visual. Each calculation item is a DAX expression where SELECTEDMEASURE () stands in for that measure:

-- Calculation group 'Time Calculation', column [Period]

-- Item: Current
SELECTEDMEASURE ()

-- Item: FYTD
VAR LastDate = CALCULATE ( MAX ( Admissions[Admission Date] ), REMOVEFILTERS () )
RETURN
    IF (
        NOT ISSELECTEDMEASURE ( [Waiting List] ) && MIN ( 'Date'[Date] ) <= LastDate,
        CALCULATE ( SELECTEDMEASURE (), DATESYTD ( 'Date'[Date], "2026-03-31" ) )
    )

-- Item: Same weekday last year
CALCULATE ( SELECTEDMEASURE (), DATEADD ( 'Date'[Date], -364, DAY ) )

-- Item: YoY %  (format string expression: "0.0%")
VAR PY = CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
RETURN DIVIDE ( SELECTEDMEASURE () - PY, PY )

Put [Period] on the columns of a matrix and every measure in the Values well gets every variant. What the documentation says, and what I’d plan around:

• Explicit measures only. Calculation items don’t apply to implicit measures (a column dragged into a visual and summed). When you create the first calculation group in Desktop, it asks to turn on discourage implicit measures.
• Every measure is in scope. A financial-year-to-date waiting list is meaningless: the list is a snapshot, not a flow. ISSELECTEDMEASURE lets an item blank out such measures, as the FYTD item does above.
• Formats. The YoY % item needs its own format string expression, or it inherits the base measure’s whole-number format.
• Selection rules. By default an item is applied only when exactly one item of the group is in the filter context. Selection expressions can change what happens with no selection or several.
• Measures become variant. Once the model has a calculation group, Power BI treats all measures as the variant data type. Microsoft’s docs note this can break dynamic format strings that reuse a measure.
• Precedence. With more than one calculation group, the precedence property decides which wraps which.

You can create one from the Calculation group button in Model view, in TMDL view, or with Tabular Editor. Calculation groups in practice goes deeper into precedence, dynamic format strings and the other pitfalls, with TMDL from a complete example model. For RTT measures specifically, see DAX for waiting-list KPIs and the RTT waiting list semantic model project.

Checklist

• The date table covers whole financial years, has no gaps, is marked, and Auto date/time is off.
• Every year-to-date function has the 31 March year end.
• Measures return BLANK after the last date with data.
• Current-period comparisons use like-for-like dates.
• Daily and weekly comparisons shift by 364 days; monthly and yearly use SAMEPERIODLASTYEAR.
• Time variants live in one calculation group, with snapshot measures excluded.