Forum Discussion
7 Digit Julian Date conversion
If a query (M) solution is also fine, then you can add a custom column with the following code, in which "JulianDate" is your field with the 7-digit date:
= Date.AddDays(#date(Number.RoundDown([JulianDate]/1000),1,1),Number.Mod([JulianDate],1000)-1)
Super UserHi Sean
I would follow logic similar to MarcelBeug but slightly different:
CalendarDate = DATE ( INT ( DIVIDE ( TheTable[JulianDate], 1000 ) ), 1, MOD ( TheTable[JulianDate], 1000 ) )
Or this version would be about the same as Marcel's.
CalendarDate =
DATE ( INT ( DIVIDE ( TheTable[JulianDate], 1000 ) ), 1, 1 )
+ MOD ( TheTable[JulianDate], 1000 )
- 1In this case, with the DATE function in DAX (and Excel for that matter) you can get away without explicitly working out the months/days.
It will automatically "roll over" the years/months as required if either the month argument is outside 1..12 or the day argument is outside the date range of the month.
For example DATE(2016, 1, 100) is equivalent to DATE(2016, 4, 9), i.e. the 100th day counting from 1st Jan 2016.
Cheers,
Owen :)
Community Champion
OwenAuger wrote:For example DATE(2016, 1, 100) is equivalent to DATE(2016, 9, 4), i.e. the 100th day counting from 1st Jan 2016.
As always OwenAuger - that works great! Thank You! :smileyhappy:
Indeed the DATE function takes care of this I did not know/remember this!
the 100th day in 2016 - April 9, 2016 - DATE ( 2016, 1, 100 )
the 100th day in 2015 - April 10, 2015 - DATE ( 2015, 1, 100 )
Thanks Owen!
Sean