Spreadsheets

DAX TOTALYTD with an April to March financial year end

Use DAX TOTALYTD with a 31 March year end so year-to-date spend resets each April. See a month-by-month pivot result and what to check if it misbehaves.

Editorial illustration for DAX TOTALYTD with an April to March financial year end

TOTALYTD takes an expression, a date column and an optional year end date, so to make the running total restart every April you pass the text "3/31" as the year end. The measure then climbs from April through March and resets on 1 April, which is how an Indian financial year works.

What is the formula?

You need a Total Spend measure and a proper marked date table first. With those in place:

  • Total Spend = SUM(Transactions[Amount])
  • FY YTD Spend = TOTALYTD([Total Spend], Calendar[Date], "3/31")

The third argument is a year end written as text. DAX documentation describes it as a date in the same locale as the file's author, and the year part is ignored. So "3/31" is month 3, day 31 on a US-style setup. If your machine uses day first, you may need "31/3" instead. Test it: put Month in Rows and see whether the total resets in April, not in January.

An equivalent form uses CALCULATE and DATESYTD:

  • FY YTD Spend = CALCULATE([Total Spend], DATESYTD(Calendar[Date], "3/31"))

What does the result look like in a pivot?

Put Financial Year and Month in Rows, with Total Spend and FY YTD Spend in Values. Using hypothetical monthly totals:

MonthTotal SpendFY YTD Spend
Apr 202520,00020,000
May 202518,00038,000
Jun 202522,00060,000
Mar 202619,000Full-year total
Apr 202621,00021,000 (restarts)

Check the arithmetic: 20,000 plus 18,000 is 38,000, and 38,000 plus 22,000 is 60,000. In April of the next year the running total falls back to that month's own spend, because a new financial year has begun.

Why is my year-to-date resetting in January?

  • The year end argument is missing, so DAX assumes 31 December. Add "3/31".
  • The locale reads your text the other way round. Try the day-first form.
  • The date table is not marked, or has gaps. Mark it as a date table and make sure it covers whole years.
  • The Date column used is the one in Transactions, not the Calendar. Use the Calendar column, and link the two tables.

Why do future months show a number too?

TOTALYTD returns the running total up to each date, so a month with no transactions repeats the last total. If your calendar runs to next March, the empty months show the same figure as the last month with data. Wrap the measure so empty months stay blank:

  • FY YTD Spend = IF(ISBLANK([Total Spend]), BLANK(), TOTALYTD([Total Spend], Calendar[Date], "3/31"))

ISBLANK checks whether the month has any spend at all, and BLANK() leaves the cell empty.

What is a sensible way to verify it?

  1. Pick one financial year and filter the pivot to it.
  2. Add up three months by hand from your statements.
  3. Compare with the third row of FY YTD Spend.
  4. Check that the March row equals the year's full total.

What happens at the year boundary?

The reset is the whole point of the year end argument. With the default of 31 December, a pivot of April 2025 to March 2026 would show the running total climb from April to December, drop back to a small figure in January, and then climb again, which is useless for an Indian financial year. With "3/31" the running total climbs for all twelve months and drops only in April. If you also need a calendar-year view, keep a second measure with no year end argument and give the two clear names such as FY YTD Spend and Calendar YTD Spend.

Can I compare year to date with last year?

Yes, once the date table is solid. A measure that wraps the year-to-date figure in a previous-year shift gives the figure for the same stretch of the earlier financial year. That is covered in the companion guide on comparing with the same period last year.

If a measure language is more than your expenses need, SpendixAI offers ready-made reports and a builder over imported statement data, with dates grouped by your own filters, and you can export a CSV for any extra analysis in a sheet.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports