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

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

Closed

as By Design Help for as By Design

Type:	Bug
ID:	538648
Opened:	3/3/2010 1:40:05 PM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

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

Details (expand)

English

SQL Server 2008 R2 RC – “PowerPivot” Add-in

Analysis Services

Windows 7 (all editions)

US English

Create a table with 1 column as data type=date
Create a calculated column using the following formula
=DATEADD(<prev. created column name.,-1,YEAR)

Many blank cells

The correspondent cell date shifted 1 year back.

X64

File Name	Submitted By	Submitted On	File Size
bugDAXDateAdd.docx (restricted)	nicola dell_anna	3/3/2010	-
bugDateAdd.xlsx (restricted)	nicola dell_anna	3/3/2010	-

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)

Observe:
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.

Thanks,
Howie Dickerman
Senior Program Manager, Analysis Services Team
mailto:howied@microsoft.com