Remarks

The dates argument can be any of the following:

• A reference to a date/time column. In this case, a context transition applies because the column reference is replaced by
  • CALCULATETABLE ( DISTINCT ( <Dates> ) )
• A table expression that returns a single column of date/time values.
• A Boolean expression that defines a single-column table of date/time values.
• A calendar reference. You define a calendar in the model for calendar-based time intelligence. With a calendar, the result has the primary column of each calendar category and the time-related columns of the calendar. Also in this case, a context transition applies.

The result table includes only dates that exist in the dates column.

With a date column, the syntax:

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

corresponds to:

DATESBETWEEN ( 
    <Dates>, 
    STARTOFYEAR ( LASTDATE ( <Dates> )[, <YearEndDate>] ),
    LASTDATE ( <Dates> ) 
)

YearEndDate is valid only with a date column. With a calendar, DATESYTD returns an error if you use YearEndDate. The calendar defines the year, for example a fiscal year.

YearEndDate is the last day of the year. If you omit YearEndDate, or if it is BLANK, the year ends on December 31. YearEndDate must be a constant string or a date literal with the dt prefix, for example “6-30”, “06/30”, “June 30”, “2008-06-30″, or dt”2008-06-30”. A variable with a constant string is also valid. An expression like DATE ( 2008, 6, 30 ) or a string concatenation returns an error. DAX ignores the year part.

DAX reads the YearEndDate string with the locale of the model. The YYYY-MM-DD format does not depend on the locale. With other formats, a day that is not valid can change the result without an error. For example, with “6-31” the year ends on June 1.

Do not use February 29 in YearEndDate. With “2-29”, the year ends on February 1. With “2020-02-29”, some years start on March 2 instead of March 1. Use February 28: in a leap year, February 29 is then the first day of the next year.

With a calendar, the last date of the result depends on the columns that the filter context filters:

• If the filter context filters the column tagged as Day, the result ends with the last visible day.
• If the filter context filters a column of another category, for example a week or a month column, the result ends with the last day of the last visible period of that category. The result does not go past the end of the year.
• If the filter context filters only columns that no calendar tags, for example the day of the week, the result has all the days of the year that includes the last visible date.

For example, with a filter on the DateKey column from August 1 to August 13, 2008, DATESYTD ( Gregorian ) returns January 1 to December 31, 2008, and DATESYTD ( ‘Date'[Date] ) returns January 1 to August 13, 2008. With a date column, the result always ends with the last visible date.

If the filter context filters a column that another calendar tags as Week, a calendar without the Week category returns an error.

With a calendar, CALCULATE removes only the filters on the columns of the calendar. The filters on the other columns of the date table stay, for example a filter on the day of the week. With the date column of a table marked as a date table, CALCULATE removes all the filters on the date table.

This function is not supported in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.