The DATESYTD function returns a table that contains dates from the beginning of the year in the current context to the last date in the current context.
DATESYTD(
dates
[, year_end_date]
)
- dates: Column containing dates.
- year_end_date: Optional argument of type string. Date that defines the last day of the year to be considered (if it is not specified, it is understood that it is December 31).
The DATESYTD function returns a table.
The dates argument can be a reference to a column containing dates, an expression that returns a table with a single column containing dates, or a Boolean expression that defines a table with a single column containing dates.
The year_end_date argument is a text string in the same format as that used for dates on the computer where the workbook was created. Only the day and the month are considered (the value corresponding to the year will be ignored, so we can indicate any). This argument allows us to treat June 30 as the last day of the year, for example, which is common in fiscal calendars.
This function considers the last day of the current context and returns a table containing all the dates from the first day of the year to which that day belongs to this last day. This means that if in the current context more than one year is involved, only the last one is considered.
Depending on the context in which it is used, the result may not be as expected (for example, in a matrix of months and years in which we want to show accumulated sales since the beginning of the year, we may simply see sales by month). In such a case, we must make sure that we have marked the calendar table as "date table" (by selecting the table in the Fields column and executing the Table Tools > Calendars > Mark as date table command):
In this example, a measure has been defined that sums the column containing the sales amounts:
Total Sales = SUM(FactSales[SalesAmount])
We now create the following measure that calculates the sum of the mentioned column but applying the filter returned by the DATESYTD function (that is, it calculates the sum of the sales amounts from the first day of the year of the current context to the last day of the current context ):
Total Sales YTD =
CALCULATE(
[Total Sales],
DATESYTD(DimDate[Datekey])
)
Consider the following table containing sales figures and the date on which they were made:
As can be seen, it includes sales information for 2015 and 2016.
We define the Ventas measure:
Ventas = SUM(Ventas[Importe])
...and then we create the Ventas YTD measure that calculates accumulated sales since the beginning of the year:
Ventas YTD = CALCULATE(
Ventas[Ventas],
DATESYTD(Calendario[Fecha])
)
If we take these measures to a matrix, we obtain the following:
You can see how the Ventas YTD measure accumulates the sales made since January 1 of each year.
If we now define the following measure:
Ventas YTD2 = CALCULATE(
Ventas[Ventas],
DATESYTD(Calendario[Fecha]; "01/11/2015")
)
...we check how sales accumulate considering November 1 as the end of the year:
