TOTALYTD in DAX

The most-requested figure on any dashboard is year-to-date: the running sum of a measure from the start of the year up to the date in context. DAX gives you a purpose-built wrapper, TOTALYTD:

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

Read it like this. Evaluate [Total Sales], but over the set of dates running from 1 January of the current year through the last date in the current filter context. Drop the measure into a PivotTable with months down the rows and you get the classic staircase. January shows January, February shows Jan+Feb, March shows Jan+Feb+Mar, and the whole thing resets cleanly each new year. Because it respects filter context, the same measure gives a per-product YTD, a per-region YTD, and a grand-total YTD all at once.

TOTALYTD is a convenience shell over the more primitive DATESYTD, which returns the set of dates and is used inside CALCULATE. These two are equivalent:

YTD Sales      = TOTALYTD( [Total Sales], 'Date'[Date] )
YTD Sales (alt) = CALCULATE( [Total Sales], DATESYTD( 'Date'[Date] ) )

Why learn the longer CALCULATE+DATESYTD form at all? Because it composes. You can add other filters alongside the date filter, which the convenience wrapper makes awkward.

How do you set a fiscal year end in TOTALYTD?

For Indian reporting, the fiscal-year argument is essential. By default the year restarts on 1 January, but Indian companies close their books on 31 March. TOTALYTD takes an optional final year_end_date argument, a text date like "3/31", that moves the reset:

Fiscal YTD Sales = TOTALYTD( [Total Sales], 'Date'[Date], "3/31" )

Now the running total restarts every 1 April. The YTD at June sums April + May + June of that fiscal year, where the calendar version sums January through June. The same trick handles a US retailer on a January year-end ("1/31") or any 52/53-week convention. Get this argument right and the dashboard matches the audited accounts. Get it wrong and it disagrees with them by a quarter, without a single error to tip you off.

To see it work, build the measure in Power Pivot ▸ Manage ▸ Measures ▸ New Measure. Or, in the field list, right-click the table ▸ Add Measure. Set its Number Format in that same dialog (currency, 0 decimals), then drop it into a PivotTable beside [Total Sales] with the Date hierarchy on rows. Watch the two columns. One stays flat per month and the other keeps climbing.