This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. Creating date-based functions in DAX in SSAS Tabular Cumulative totals (TotalYTD, DatesYTD, etc) (this blog) The same period in a previous month, quarter or year Using PARALLELPERIOD to get the whole of the last period Moving averages using DAX date functions 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.

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.

Creating date-based functions in DAX in SSAS Tabular
Cumulative totals (TotalYTD, DatesYTD, etc) (this blog)
The same period in a previous month, quarter or year
Using PARALLELPERIOD to get the whole of the last period
Moving averages using DAX date functions
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

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.

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

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Cumulative totals (TotalYTD, DatesYTD, etc)

The DatesYTD, DatesQTD and DatesMTD functions

The TOTALYTD, TOTALQTD and TOTALMTD functions

Head office

London

Manchester