DAX Date & Time - DATE function

Description

Returns the specified date in datetime format.

Syntax

DATE (<year>, <month>, <day>)

Parameters

Sr.No. Parameter & Description
1

year

A number representing the year.

The value of the year argument can include one to four digits. The year argument is interpreted according to the date system used by your computer.

Dates beginning with March 1, 1900 are supported.

If you enter a number that has decimal places, the number is rounded.

For values greater than 9999 or less than zero (negative values), the function returns a #VALUE! error.

If the year value is between 0 and 1899, the value is added to 1900 to produce the final value.

Note − You should use four digits for the year argument whenever possible to prevent unwanted results. For example, using 15 for 2015 returns 1915 as the year value, which is not the case.

2

month

A number representing the month or a calculation according to the following rules −

If month is a number from 1 to 12, then it represents a month of the year. 1 represents January, 2 represents February, and so on until 12 that represents December.

If you enter an integer larger than 12, the following computation occurs −

The date is calculated by adding the value of month to the year. For example, if you have DATE (2015, 19, 1), the function returns a datetime value equivalent to July 1st of 2016, because 19 months are added to the beginning of 2015, yielding a value of July 2016.

If you enter a negative integer, the following computation occurs −

The date is calculated subtracting the value of month from the year. For example, if you have DATE(2015, -6, 15), the function returns a datetime value equivalent to June 15th of 2014, because when 6 months are subtracted from the beginning of 2015 it yields a value of June 2014.

3

day

A number representing the day or a calculation according to the following rules −

If day is a number from 1 to the last day of the given month then it represents a day of the month.

If you enter a number larger than the last day of the given month, the following computation occurs −

The date is calculated by adding the value of day to month. For example, in the formula DATE(2016, 8, 45), the DATE function returns a datetime value equivalent to September 15th of 2016, because 45 days are added to the beginning of August yielding a value of September 15th.

If you enter a negative number, the following computation occurs −

The date is calculated subtracting the value of day from month. For example, in the formula DATE(2016, 5, -15), the DATE function returns a datetime value equivalent to April 15th of 2016, because 15 days are subtracted from the beginning of May 2016 yielding a value of April 2016.

If day contains a decimal portion, it is rounded to the nearest integer value.

Return Value

Specified date in datetime format.

Remarks

The DATE function takes the numbers that are input as arguments and generates the corresponding date. The DATE function is most useful in situations where the year, month, and day are supplied by DAX formulas.

For e.g. the underlying data might contain dates in a format that is not recognized by DAX as a date, such as YYYYMMDD. You can use the DATE function in conjunction with other DAX functions to convert the dates to datetime format that can be recognized as a date by DAX.

DAX date functions always return a datetime data type. However, you can use formatting to display dates as serial numbers if you want.

Example

= DATE (2016,8,5) returns 8/5/2016 12:00:00 AM
= DATE (2016,8,45) returns 9/14/2016 12:00:00 AM
= DATE (2016,8, -5) returns 7/26/2016 12:00:00 AM
= DATE (2016,15,15) returns 3/15/2017 12:00:00 AM
dax_functions_date_time.htm

Useful Video Courses

Video

Mastering DAX and Data Models in Power BI Desktop

53 Lectures 5.5 hours

Video

Mastering DAX Studio

Featured

24 Lectures 2 hours

Video

DAX / Power BI - Customer and Sales Analysis Deep Dive

26 Lectures 4.5 hours