The hidden secrets of TOTALYTD

DAX has many time intelligence functions that are often redundant, offering different shorter syntaxes for longer more generic functions. However, sometimes the shorter syntax could be dangerous, as I explain in this blog post where I suggest using CALCULATE and DATESYTD instead of TOTALYTD. The measures used in this post are included in a sample demo file.

For example, the following two functions are identical:

SAMEPERIODLASTYEAR ( 'Date'[Date] )
DATESADD ( 'Date'[Date], -1, YEAR )

A common function I see often is this:

TOTALYTD ( [measure], 'Date'[Date] )

Which corresponds to:

CALCULATE (
    [measure],
    DATESYTD ( 'Date'[Date] )
)

Generally, I prefer the CALCULATE approach, because it’s more flexible. However, in my early days with DAX I wrongly assumed that TOTALYTD did not have the ability to add other filters as CALCULATE does. In reality, TOTALYTD can add just one filter. Thus, you can rewrite this measure:

Red Sales YTD :=
CALCULATE (
    [Sales Amount],
    DATESYTD ( 'Date'[Date] ),
    Product[Color] = "Red"
)

using TOTALYTD instead of CALCULATE this way:

Red Sales YTD 2 :=
TOTALYTD (
    [Sales Amount],
    'Date'[Date],
    Product[Color] = "Red"
)

However, we cannot rewrite the following measure using TOTALYTD, because it has two filter arguments other than the DATESYTD filter:

US Red Sales YTD :=
CALCULATE (
    [Sales Amount],
    DATESYTD ( 'Date'[Date] ),
    Product[Color] = "Red",
    Customer[Country] = "United States"
)

In reality, with some creative technique everything is possible in DAX. We can create a complex filter in the single filter argument available for TOTALYTD, combining two single column filters in a table of two columns using CROSSJOIN.

US Red Sales YTD 2 :=
TOTALYTD (
    [SalesAmount],
    'Date'[Date],
    CROSSJOIN ( 
        TREATAS ( { "Red" }, Product[Color] ),
        TREATAS ( { "United States" }, Customer[CountryRegion] )
    )
)

You have been warned. I don’t like the code above. If you want to apply filters, use CALCULATE. Maybe the code written using CALCULATE is verbose and boring, but it’s easier to read. If you are looking for an enjoyable reading for your free time, you can choose a novel rather than playing with an obscure DAX syntax.

So, why writing a blog post about this? The reason is that TOTALYTD has the ability to accept a scalar value as an argument instead of (or after) the filter. This is the same additional optional parameter accepted by DATESYTD in case the fiscal year does not end on December 31st. The problem is that the third argument of TOTALYTD is a filter if you use a predicate or a table expression, and it’s a marker for end of year in case it’s a scalar value. Confusing enough? Ok, let me present some example.

This is how I prefer to write a YTD for a fiscal year starting on July 1st.

Sales FYTD := 
CALCULATE (
    [SalesAmount],
    DATESYTD ( 
        'Date'[Date],
        "06/30"
    )
)

And this is the syntax I don’t like:

Sales FYTD 2 := 
TOTALYTD (
    [SalesAmount],
    'Date'[Date],
    "06/30"
)

The third argument of TOTALYTD could be any scalar value, which is converted into a date. An invalid string generates an error, but what is the expected behavior of this?

Sales FYTD what := 
TOTALYTD (
    [SalesAmount],
    'Date'[Date],
    2008
)

The result is still a year-to-date for a fiscal year starting on July 1st. How can this be possible?
Well, the 2008 integer value is converted in a date used to extract month and day to use as end of date. And the conversion of 2008 is June 30th, 1905. Here is a measure to test this:

What is 2008 :=
CALENDAR ( 2008, 2008 )

The result of this measure is a single date. I know, CALENDAR is not supposed to be used this way, but I don’t want to digress too much. Going back to TOTALYTD, what I don’t like of its syntax is that it is error-prone. A mistake typing a complex filter might result in a number instead of a Boolean, which would be interpreted as an end-of-year parameter instead of a filter. This is less likely to happen with CALCULATE and DATESYTD, especially using the correct formatting – because the arguments of DATESYTD cannot be filters and the arguments of CALCULATE cannot be scalar values.

The sample demo file includes all the formula shown in this blog post.