Home Dashboard Directory Help
Search

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


Status: 

Closed
 as By Design Help for as By Design


1
0
Sign in
to vote
Type: Bug
ID: 538648
Opened: 3/3/2010 1:40:05 PM
Access Restriction: Public
0
Workaround(s)
view
0
User(s) can reproduce this bug

Description

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
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)

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
Sign in to post a workaround.
File Name Submitted By Submitted On File Size  
bugDAXDateAdd.docx (restricted) 3/3/2010 -
bugDateAdd.xlsx (restricted) 3/3/2010 -