Power Pivot Principles: The A to Z of DAX Functions – DATESYTD

In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions.  This week, we look at DATESYTD.

The DATESYTD function

The DATESYTD function is one of the time intelligence functions.  It returns a set of dates in the year up to the last date visible in the filter context.  This function is similar to the DATESMTD and the DATESQTD.  It operates under the following syntax:

There is one [1] argument for the DATESYTD function:

• dates: this argument is required, and it is the name of a column or one column table containing dates.  This can be any of the following:
• a reference to a date / time column
• 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.
• year_end_date: this is an optional, literal string that defines the year-end date.  If omitted, the default is 31 December.  It may be specified using =DATESYTD(Calendar[Date], “30 June 1976”), for example.

The resulting table includes only dates that exist within the dates argument.

Since the DATESYTD is a time intelligence function, there are few key notes you should consider when using it (or any time intelligence function):

• all dates need to be present for the years required.  All the days in this span, from January 1 to December 31, must be included in the Date table.  The date table must include all dates from commencement to the last day of a fiscal year if the report solely refers to fiscal years
• a column with a DateTime or Date data type and unique values is required.  Typically, this column is known as Date.  Although it is common practice, this is not necessary when defining associations with other tables.  However, the ‘mark as Date Table’ feature should relate to the Date column, which must have distinct values
• the Date table must be designated as a date table in the model in case the relationship between the Date table and any other table is not dependent upon the Date.

Consider the following example: we have a table named Date_Table that contains dates ranging from 1 January 2020 to 15 May 2021:

Using the DATESYTD function on this table as follows will yield a table containing all of the days of the latest year within the data:

We have hidden some rows here, however as you can see, the resulting table contains the dates of 2021 up to the final date defined.

Come back next week for our next post on Power Pivot in the Blog section.  In the meantime, please remember we have training in Power Pivot which you can find out more about here.  If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.