Sign in
 

none
DAX Dateadd function question

 , 
 > 

    Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I need to convert a number to a date - the problem is that the number to be converted represents the days since 1/1/1800 (this is a Time Matters/LexisNexis database).  So the 'calculation' needs to be 1/1/1800 + the number of days (event(date)) = date.  I tried DATEADD('1/1/1800',event(date),day) which returned an error (I believe because of the hard coded date).  I also tried creating a date column to house the 1/1/1800 date by using the DATE function but don't see that the year 1800 is supported.  Any ideas? 

    Thursday, October 03, 2013 2:54 AM
    |

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi cleinart,

    the first data PowerPivot is aware of is December 31st of 1899
    you can test this by creating a calculated measure like this:

    Test:=DATEVALUE("1899-12-31")

    any value before this date throws an error

    in order to add days to a given date-value, you can simply add the number of days to that date-value by using "+" operator:

    =[DateColum]+100

    to solve your issue you would need to get the number of days (<<NumDaysDiff]>>) between your start date (1/1/1800) and any supported fixed date (e.g. 1/1/1900)
    to get the final date you can then use =DATEVALUE("1900-01-01") + ([event(date)] - <<NumDaysDiff>>)

    a bit inconvenient but the only option I could think of

    hth,
    gerhard

    - www.pmOne.com -

    Thursday, October 03, 2013 2:59 PM
    |
    Answerer

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi cleinart,

    the first data PowerPivot is aware of is December 31st of 1899
    you can test this by creating a calculated measure like this:

    Test:=DATEVALUE("1899-12-31")

    any value before this date throws an error

    in order to add days to a given date-value, you can simply add the number of days to that date-value by using "+" operator:

    =[DateColum]+100

    to solve your issue you would need to get the number of days (<<NumDaysDiff]>>) between your start date (1/1/1800) and any supported fixed date (e.g. 1/1/1900)
    to get the final date you can then use =DATEVALUE("1900-01-01") + ([event(date)] - <<NumDaysDiff>>)

    a bit inconvenient but the only option I could think of

    hth,
    gerhard

    - www.pmOne.com -

    Thursday, October 03, 2013 2:59 PM
    |
    Answerer
  • Question
    Sign in to vote
    0
    Sign in to vote

    DATEADD() requires a separate Calendar table with a unique row for every single date you are using in calculations.

    To even consider using it in this situation, you would need a Calendar table spanning from 1/1/1800 to the present (or some date in the future).

    That being said, the Excel Date-Time Code begins at 1/1/1900 so I'm not sure if a DATEADD would work on dates before that even with a Calendar going back that far.  It might...I don't know. It's a little out of the range I typically work with so you would have to give it a try.

    Sounds like the easiest solution would be to use a little simple math to get the number into the stand Date-Time Code format and then format that number as a date.

    There are 36,524 days between 1/1/1800 and 1/1/1900, so if your number does actually reflect the days since 1/1/1800 then subtract 36,524 from it and convert the resulting number into a date format.

    Thursday, October 03, 2013 3:02 PM
    |
    Answerer