Answered by:
DAX Dateadd function question

-
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?
Question
Answers
-
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 -
- Proposed as answer by Elvis LongMicrosoft contingent staff, Moderator Thursday, October 17, 2013 2:41 AM
- Marked as answer by Elvis LongMicrosoft contingent staff, Moderator Sunday, October 20, 2013 12:50 PM
All replies
-
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 -
- Proposed as answer by Elvis LongMicrosoft contingent staff, Moderator Thursday, October 17, 2013 2:41 AM
- Marked as answer by Elvis LongMicrosoft contingent staff, Moderator Sunday, October 20, 2013 12:50 PM
-
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.