If you found this blog useful and you’d like to say thanks you can click here to make a contribution. Thanks for looking at our blogs!

BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

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.

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: 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: 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(

SUM([Quantity]),

DATESYTD(Calendar[Date])

)

Here's the formula as a measure: 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.

## The TOTALYTD, TOTALQTD and TOTALMTD functions

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: 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:

YTD:=TOTALYTD(

SUM([Quantity]),

Calendar[Date]

)

Here are the two alternative approaches shown side by side, just to show that they give the same result! 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.