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 values 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 = 2nd Jan 2021 because 33 days are added to the beginning of December 2020.
In a similar way , for negative day value, ie 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
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.
GetDate_YearGreaterThan9999 = DATE(10000, 2, 3)
IF a value for Year is less than 0 (negative), then Date function gives an following error.
GetDate_NegativeYear = DATE(-1989, 2, 3)
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
