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)
Community ChampionIf 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)
SeanMarcelBeug 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
- MarcelBeug
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!
- Sean
MarcelBeugThanks!
Maybe because it takes me 4 steps (3 conditional columns) and then in the 4th concatenate the final result to do this with DAX.
Split the number into year and the remaining 3 digits - Check if its a Leap Year
and then 2 long switch statements to convert the 3 digits - once into months and then - into days in each month
depending on whether it is a leap year or not
and then I concatenate and convert to Data Type: Date
But then again there may be an easier DAX solution
If anyone knows of one it may be OwenAuger or Anonymous or Anonymous or Vvelarde
Is there a way to remove the error if there is no number in the field?
- MarcelBeug
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.
I thought there was no number. It actually had a 0 so i modified it
if [DRTDAT] = 0 then " " else Date.AddDays(#date(Number.RoundDown([DRTDAT]/1000),1,1),Number.Mod([DRTDAT],1000)-1)
