cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: 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)
Specializing in Power Query Formula Language (M)

View solution in original post

12 REPLIES 12
Highlighted
Community Champion
Community Champion

Re: 7 Digit Julian Date conversion

Just heads up - 2016 was a leap year!

 

Good Luck! Smiley Happy

Highlighted
Regular Visitor

Re: 7 Digit Julian Date conversion

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

Highlighted
Community Champion
Community Champion

Re: 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)
Specializing in Power Query Formula Language (M)

View solution in original post

Highlighted
Community Champion
Community Champion

Re: 7 Digit Julian Date conversion

@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

 

Highlighted
Community Champion
Community Champion

Re: 7 Digit Julian Date conversion

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!

 

Specializing in Power Query Formula Language (M)
Highlighted
Community Champion
Community Champion

Re: 7 Digit Julian Date conversion

@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 @Steve_Wheeler or @Vvelarde

Highlighted
Super User I
Super User I

Re: 7 Digit Julian Date conversion

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 🙂


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn
Highlighted
Community Champion
Community Champion

Re: 7 Digit Julian Date conversion


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

 

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

Highlighted
Regular Visitor

Re: 7 Digit Julian Date conversion

Thanks everyone . @MarcelBeug Your version worked perfect! 

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors