7 Digit Julian Date conversion

[medwardsOT]

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

[medwardsOT]

Thanks everyone . @MarcelBeug Your version worked perfect! 

[MarcelBeug · Accepted solution]

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)

[medwardsOT]

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.

[medwardsOT]

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)

[Sean]

@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

[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 @KHorseman or @Anonymous or @Vvelarde

[OwenAuger]

Hi @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 )
    - 1

In 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 🙂

[Sean]

---

@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!

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

[Sean]

Just heads up - 2016 was a leap year!

Good Luck!

[medwardsOT]

Yes it was but i will need it for previous years as well.