YTD Measure in DAX Studio doesn't show any value for several dates

Question

Trying to calculate YTD for several dates in DAX STUDIO according to https://www.daxpatterns.com/time-patterns/ guide

I've created a measure in my model, which gets the job done when I use Date Dimension and calculates YTD for every date

CALCULATE (
    [Sales Amt],
    FILTER (
        ALL ( 'Date' ),
        'Date'[Year] = MAX ( 'Date'[Year] )
            && 'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

But when I'm trying to reproduce the same result using DAX Studio, I get correct result only when I filter CALCULATETABLE for a certain date

This code works perfectly:

EVALUATE
CALCULATETABLE(
                ADDCOLUMNS (
                    VALUES ( 'Date'[Date] ),
                    "YTD", CALCULATE (
                        [Sales Amt],
                        FILTER (
                            ALL ( 'Date' ),
                            'Date'[Year] = MAX ( 'Date'[Year] )
                                && 'Date'[Date] <= MAX ( 'Date'[Date]  )
                        )
                    )
                )
,
'Date'[Date] = DATE(2018,5,1)
)

This code is expected to return YTD for every date in rows, but, unfortunately it doesn't. What am I doing wrong?:

EVALUATE
CALCULATETABLE(
                ADDCOLUMNS (
                    VALUES ( 'Date'[Date] ),
                    "YTD", CALCULATE (
                        [Sales Amt],
                        FILTER (
                            ALL ( 'Date' ),
                            'Date'[Year] = MAX ( 'Date'[Year] )
                                && 'Date'[Date] <= MAX ( 'Date'[Date]  )
                        )
                    )
                )
,
'Date'[Date] >= DATE(2018,5,1)
)

The only difference is = vs >=, but I get empty result for all the rows

Dan Dan · Accepted Answer · 2018-06-29T07:56:18

Nothing is wrong with the way you defined your measure.

However, when testing the measure, you should try to write your DAX query in a way similar to what client tools like Power BI would do:

EVALUATE
SUMMARIZECOLUMNS(
    'Date'[Date],
    FILTER('Date', 'Date'[Date] >= DATE(2018,5,1)),
    "YTD", CALCULATE (
        [Sales Amt],
        FILTER (
            ALL ( 'Date' ),
            'Date'[Year] = MAX ( 'Date'[Year] )
                && 'Date'[Date] <= MAX ( 'Date'[Date]  )
        )
    )
)

or even better:

DEFINE MEASURE 'Sales'[Sales Amt YTD] = CALCULATE (
        [Sales Amt],
        FILTER (
            ALL ( 'Date' ),
            'Date'[Year] = MAX ( 'Date'[Year] )
                && 'Date'[Date] <= MAX ( 'Date'[Date]  )
        )
    )

EVALUATE
SUMMARIZECOLUMNS(
    'Date'[Date],
    FILTER('Date', 'Date'[Date] >= DATE(2018,5,1)),
    "YTD", [Sales Amt YTD]
)

The issue with the way you wrote it, using CALCULATETABLE, is that you create an outside filter context on the 'Date'[Date] column that contains all dates starting from 2018-05-01. When evaluating the YTD logic inside the FILTER statement, MAX('Date'[Year]) uses this filter context, so that it returns whatever year is the largest on your entire 'Date' table (for example 2025).

Remember, that the CALCULATE function only applies a context transition (row context to filter context) when evaluating the first argument. The filter arguments are evaluated on the original filter context.

If you want to stick with your syntax, you could write an additional CALCULATE to force the context transition to apply also to the 2nd argument of the inner CALCULATE call:

EVALUATE
CALCULATETABLE(
    ADDCOLUMNS (
        VALUES ( 'Date'[Date] ),
        "YTD", 
            CALCULATE(        // Additional CALCULATE to force context transition on filter arguments
                CALCULATE (
                [Sales Amt],
                FILTER (
                    ALL ( 'Date' ),
                    'Date'[Year] = MAX ( 'Date'[Year] )
                        && 'Date'[Date] <= MAX ( 'Date'[Date]  )
                )
            )
        )
    ),
    'Date'[Date] >= DATE(2018,5,1)
)

This would be the same as referencing the measure directly, as referencing a measure always does an implicit CALCULATE:

EVALUATE
CALCULATETABLE(
    ADDCOLUMNS (
        VALUES ( 'Date'[Date] ),
        "YTD", [Sales Amt YTD]
    ),
    'Date'[Date] >= DATE(2018,5,1)
)

YTD Measure in DAX Studio doesn't show any value for several dates

1 Answers