DATE is a Power BI Date and Time function which returns the date in specified datetime format.
SYNTAX
DATE(<year>, <month>, <day>)
<year> is a four digit integer number, represent year.
Date beginning with 1st march 1900 is supported, so in case if the year value is between 0 and 1899, the value is added to 1900 to produce the date.
If value of year is 2 then 1900 will be added to 2, the final year will be returned 2 + 1900 = 1902.
Returns an error, if value is greater than 9999 or less than zero (negative values).
<month> is a two digit integer number, represent a month.
If month is any number between 1 to 12, then it represents a month of the year like value 1, represents January, 2 represents February, and so on until 12 that represents December.
In case, if value for month is greater than 12, then the date is calculated by adding the value of month to the year i.e. For Date(2020,13,01), the value of month is 13ย as in second argument,ย it returns a datetime value that is 1st January 2021 because 13 months are added to the beginning of 2020.
In similar way, if a negative month value is passed to Date function i.e. DATE(2020, -10, 03), it returns 3rd February 2019, because 10 months are subtracted from the beginning of 2020.
<day> is a two digit integer number, represent a day.
If any value for a day that is larger than the last day of given month, i.e. DATE(2020, 12, 33) returns a datetime value as 2nd Jan 2021 because 33 days are added to the beginning of December 2020.
In a similar way , for negative day value, i.e. DATE(2020,12,-3 ) returns a datetime value = 27th November 2020, becauseย 3 days are subtracted from the beginning of December 2020.
Lets go through an example to see DATE function in action.
Following DAX returns a simple date
GetDate = DATE(2020, 12, 1)
It returns a datetime value as shown below.
Ifย Year value is less than 1900
Lets see the output of following DAX ,for year value 13 which is less than 1900.
GetDate_YearLessthan1900 = DATE(13, 12, 1)
If year is greater than 9999 or less than 0
If a value for year is greater than 9999, lets sayย when value of year is 10000ย than Date function gives an error as shown.
If a value for year is less than 0 (negative)
In this case, Date function gives an following error.
If a value for month is greater than 12
GetDate_DayGreaterthan12 = DATE(2020, 13, 1)
If a value for month is negative
GetDate_NegativeMonth = DATE(2020, -10, 3)
A valueย for a day is greater than the last day of given month
GetDate_DayValueIsGreaterThanLastDayOfMonth = DATE(2020, 12, 33)
A valueย for a day is Negative
GetData_DayValueIsNegative = DATE(2020,12,-3)
