KJTAP - Mediterranean Shipping Company - DAX DATEADD does not return values for some dates - by nicola dell_anna

Status : 

  By Design<br /><br />
		The product team believes this item works according to its intended design.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 538648 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 3/3/2010 1:40:05 PM
Access Restriction Public


I used DATEADD(<column date>,-1, YEAR) to calculate the previous year but I do not get the result I expected.
Also DATEADD(<column date>,+1, YEAR) does not work correctly.
See attachment for example.
It seems that DATEADD returns a value only for some specific year. All of them have nothing to do with 1900 which is the starting date for this data type.

PowerPivot Version:10.50.1450.3
Sign in to post a comment.
Posted by Microsoft on 3/15/2010 at 7:41 PM
Nicola -- This item has been resolved as By Design.

Thank you for your assistance,
The Analysis Services team
Posted by Microsoft on 3/8/2010 at 11:36 AM
Thank you for providing your feedback on PowerPivot.

Note that the dates to which you wish to move must also be in the column. Could you try this with slightly different repro steps? I think this will illustrate how the DATEADD function works.

Create a table with 1 column as data type=date
Make sure that the date column has dates for more than one year. For example, create a column that includes all the dates from Jan 1 2007 thru Dec 31 2010.

Create a calculated column using the following formula
=DATEADD(<prev. created column name>,-1,YEAR)

The calculation will be blank for all the 2007 dates, because going back one year would take you to a date that does not exist in the column. The calculation will be correct for all dates beginning Jan 1 2008, because going back one year takes you to a date that does exist in the column.

Bottom line is that the date column (or Time table) needs to include not only the starting dates, but also the dates that you expect in the results.

If this doesn't clear up this issue for you, please feel free to send mail directly to me.

Howie Dickerman
Senior Program Manager, Analysis Services Team