DAX DATE function demystified

I was working intensively last few days on DAX and DATE() was one of the functions I used quite a few times. I was confused initially on how DATE() function works and it took me a while before I understood exactly how it works.The purpose of this blog post is to clarify how DATE function works in DAX powerpivot.

The definition of DATE function in DAX is as follows

DATE(year,month,day)

All the arguments are of type integer

Lets take an example and start seeing how DATE works

=DATE(2012,1,2)

this returns date as 2nd Jan 2012 which is as expected

now see thie

=DATE(12,1,2)

this returns value as 2nd Jan 1912

so what has happened here? Since we passed year as two digit number here it intepreted it as a year offset value and added it to base year value of 1900 which is why we got 1900. same is case when we pass a three digit number as well

=DATE(112,1,2)

this returns 2nd Jan 2012 as it added 112 as a offset to base year value of 1900 to get year value of 2012

now lets go one step ahead and pass a four digit value again but less that base date value

=DATE(1800,1,2)

As you can see this returns result as 2 Jan 3700. Why didnt it return 2 Jan 1800 which is what we would expect from behaviour we saw in intial statement? Reason is simple. 1900 is base date year value which Excel understands. Any four digit value > 1900 will be interpretted as direct year value ex: 1965,2011,etc. Any year value <=1899 will be interpretted as an offset and added to base year value of 1900 to arrive at return date value

Now lets have a play with month values

=DATE(200,35,12)

this returns date as 12th Nov 2102. Lets analyse how it got this value. It took 200 as year offset and added it to base date of  1st Jan 1900 to get 1st jan 2100. For the month part, 35 being an out of range value (>12), it interprets this as month offset value and adds it to date 1st Jan 2100 to get 1st Nov 2102 (35 months translates to 2 years and 11 months). Then finally it applies 12 as date to get you date value as 12 Nov 2102.

similary for days if day value exceeds the maximum day for month it interprets it as an offset and adds it

=DATE(200, 35,45)

will return you 15 Dec 2102. this is calculated aby interpreting 45 as an offset value for day and adding it to 1st Nov 2102 which we got earlier from year and month part calculations.

Now lets see what happens when we pass negative values

=DATE(-2,-5,-7)

this will give you result as #ERROR as its evident that calculated date falls below acceptable base date of 1 Jan 1900.

=DATE(2,-5,-20)

this returns value as 10 Jun 1901. Analyzing this we can see that it applied 2 as year offset to get 1 Jan 1902. It applied the other two negative values as offsets for Month and day. so it subtracted 5 months from it to get 1 Jul 1901 and further 20 day negative offset to get 10 Jun 1901

thus we can conclude the test with following results for DATE() function

Argument	Type	Range	interpretation
Year	Integer	<   0	#ERROR as calculation falls below base year
		0 to 1899	Applies as an offset to base year of 1900 to calculate the date ie value of 200 corresponds to 1900+200 = year 2100
		1900 to 9999	Interprets it as valid year value
Month	Integer	<   0	Applies it as a offset value and subtracts months from date. If resultant date falls below base date of 1 Jan 1900 it will return #ERROR
		1 to 12	Interprets it as direct month value
		13 to 9999	It applies it as a month offset to calculate resultant date.  If resultant date falls above max date limit it will throw #ERROR
Day	Integer	<   0	Applies it as a day offset and subtracts days from date. If resultant date falls below base date it will throw #ERROR
		1 to last day of calculated month (28/29,30,31)	It will interpret it as valid day part based on month under consideration
		> Last day of month	It will interpret it as a day offset and adds number of days to calculated date.  If resultant date falls above max date limit it will throw #ERROR