562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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 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).
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(
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.
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:
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.
|Parts of this blog|
25 Aytoun Street