The STARTOFYEAR function returns the first day of the year in the current context (or the first day of the first year, if the current context includes more than one) for the specified date column.
STARTOFYEAR(
dates
[, YearEndDate]
)
- dates: Column containing dates.
- YearEndDate: Optional argument. Last day of the year (value to use instead of December 31).
The STARTOFYEAR function returns a table with a single column and a single row with a date.
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 YearEndDate argument is a text string containing a date in the local computer format and allows you to specify an end of the year other than December 31st. Although this argument must include a year (for example, "10/21/2016"), only the day and month are considered.
The beginning and end of the month, the quarter and the year are shown in the following table for some dates of May 2011:
If we take the number of the month to the row headings in a matrix, the year to the column headings, and the following measure:
First day of the year = STARTOFYEAR(DimDate[Datekey])
...to the values field, we get the following result:
It can be seen how the created measure returns the beginning of the year to which the month belongs.
If, in the same scenario, we modify the measure to specify a year-end date other than December 31, for example, May 5:
First day of the year = STARTOFYEAR(DimDate[Datekey], "5/5/2014")
...the result is as follows:
Now, the first day of the year for either period is May 6.