Forum Discussion
7 Digit Julian Date conversion
Hellowive looked for an answer regarding this but only ones regarding different julian forms.
I have a date field from our 400 server thats in 7 digit date format.
Example: 2016005
That emaple is needs to be written into a normal date format mm/dd/yyyy (1/5/2016) The days are number 1-365.
IM trying to edit this field so when the data is queried it will come in this form autopmatically.
What DAX code should i use to convert this? We are adding a new column and trying to use code there if that helps.
Thanks
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)
12 Replies
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)
MarcelBeug This is by far my favorite answer of the day!
Can you explain what the number in red does?
= Date.AddDays(#date(Number.RoundDown([JulianDate]/1000),1,1),Number.Mod([JulianDate],1000)-1)
Thanks!
Date.AddDays and Number.RoundDown and Number.Mod
Hi Sean,
The first 1 is the 3rd argument (day part) for #date.
Without it, you get an error message: Expression.Error: 2 arguments were passed to a function which expects 3.
The second 1 is a correction as the function adds the days from the year-day-number (1-365) to the date of January 1st.
Without it, 2017001 would become January 2, 2017.
Actually I'm a bit surprised this would be such a remarkable formula, but anyway: thanks!
Is there a way to remove the error if there is no number in the field?
I would prefer preventing the error, e.g. if "no number" means null
= if [JulianDate] = null then null else Date.AddDays(#date(Number.RoundDown([JulianDate]/1000),1,1),Number.Mod([JulianDate],1000)-1)
If the error can not be prevented, then you can use try .. otherwise, like
= try Date.AddDays(#date(Number.RoundDown([JulianDate]/1000),1,1),Number.Mod([JulianDate],1000)-1) otherwise null
Note: I adjusted the code out of my head, so not tested. Hopefuly it's correct.
Just heads up - 2016 was a leap year!
Good Luck! :smileyhappy:
Yes it was but i will need it for previous years as well.
Thanks everyone . MarcelBeug Your version worked perfect!
