Power BI - DAX - Bizarre Behavior for YTD calculation

Question

Trying to use a simple year to date (YTD) measure. But things are not working as expected.

Data Model

dim_date (the weekend column is incorrect, but doesn't affect of principle)

fact

The YTD measure is defined as the following:

YTD = CALCULATE(
    SUM('fact'[value]),
    DATESYTD(dim_date[Date])
)

which give me the following incorrect results for YTD.

It looks like instead of calculating the YTD for the weekend and weekday separate.

Given there are16 rows with that have a date that is label as a weekend as well as fact that all of them falls in the same year, I expect the YTD and valueto be identical in the above.

Interesting enough, if I slightly change the DAX by using the dim_date[Date].[Date] instead of just dim_date[Date], everything just works as expected.

YTD = CALCULATE(
    SUM('fact'[value]),
    DATESYTD(dim_date[Date].[Date])
)

Can anyone help to explain what is actually going on here?

The example .pbix file is here:

https://drive.google.com/open?id=1y3ndL7yDE7T4x7Z2bPhMsa-NHRgGzYj0

Alexis Olson Alexis Olson · Accepted Answer · 2019-02-21T22:56:29

As dax.guide points out,

DATESYTD ( <Dates>[, <YearEndDate>] )

is equivalent to

DATESBETWEEN (
    <DATES>,
    STARTOFYEAR ( LASTDATE ( <DATES> )[, <YEARENDDATE>] ),
    LASTDATE ( <DATES> )
)

So what's happening is that since Jan 10 is the last False value for weekend and Jan 12 is the last True value for weekend and the DATESBETWEEN function returns a continuous range not filtered by the weekend evaluation context, you get all dates up to Jan 10 in one case and all dates up to Jan 12 in the other.

To make the measure take into account the weekend value instead of calculating over a contiguous date range, you can add that as a filter:

YTD = CALCULATE(
    SUM('fact'[value]),
    DATESYTD(dim_date[Date]),
    dim_date[weekend] IN VALUES(dim_date[weekend])
)

Power BI - DAX - Bizarre Behavior for YTD calculation

1 Answers