How to use DAX date-based functions in Analysis Services tabular model
Part two of a six-part series of blogs

There are some wonderful date functions in DAX, with evocative names like TotalYtd, SamePeriodLastYear and ParallelPeriod. This blog shows how to use DAX date functions to summarise data, assuming that you have already created a calendar table.

  1. Creating date-based functions in DAX in SSAS Tabular
  2. Cumulative totals (TotalYTD, DatesYTD, etc) (this blog)
  3. The same period in a previous month, quarter or year
  4. Using PARALLELPERIOD to get the whole of the last period
  5. Moving averages using DAX date functions
  6. Semi-additive measures in DAX

This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

Posted by Andy Brown on 08 February 2016

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Cumulative totals (TotalYTD, DatesYTD, etc)

This page gives two different ways to calculate yearly, quarterly or monthly totals to date:

Year to date figures

For example, the cumulative sales for March (2,259; shown shaded in green) should and does equal the sales to date for the year (that is, 733 + 699 + 827).


The DatesYTD, DatesQTD and DatesMTD functions

One way to solve this problem is to use the DatesYTD function:

DatesYTD function

The function returns the set of dates up to and including the "current" date in the specified year (the DATESQTD and DATESMTD functions return the equivalent dates for the current quarter or month).


The second argument allows you to change your year-end from the default of December, as described in this earlier blog.

Here's a formula you could use:

Year to date:=CALCULATE(




Here's the formula as a measure:

DATESYTD example function

Calculate the total quantity sold, but instead of using the default dates for the query context, use the dates for the year up to and including the last date of the current period for each query context instead.



Calculating cumulative totals to date is such a common thing to do in DAX that there are dedicated functions to accomplish this task.  Here's the syntax of one of them, the TOTALYTD function:

TOTALYTD function

Sum an expression using a given calendar date field.


As for the DatesYTD function, you can change the financial year end as described here

So our formula for the example above could be this instead:





Here are the two alternative approaches shown side by side, just to show that they give the same result!


Two year-to-date functions

The two functions give the same answers, reassuringly.


Time now to have a look at functions which give a comparison with the same period in the previous day, month, quarter or year.

This blog has 0 threads Add post