Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DarylM
Helper II
Helper II

Convert Julian Date to Calendar Date

Does anyone know how I can convert a Julian date (Example "116343") to a calendar date? 

Struggling with a Dax formula for this. 

 

Thanks!

1 ACCEPTED SOLUTION

Yes you can reference a column name instead of a constant. It is what is recommended.

View solution in original post

8 REPLIES 8

As I understand Julian date the first two numbers are the year and the last 3 represent the day of the year. If that is true you could do somthing like below where you create a date from the numbers. The below formula only counts for dates between 2000 and 2099 because of the hardcoded "20" but you can use it as a start if it works for you. This is by the way done in M or the custom column in the query editor, not in DAX. Hope that is ok

 

Date.AddDays(Date.From(DateTimeZone.From("20" & Text.Start(Number.ToText(116343),2) & "-01-01")),Int64.From(Text.End(Number.ToText(116343),3)))

 

Hope you can use it for something

I found this string while trying to convert from a 7-digit ordinal date to a Gregorian Date.  I repurposed the formula as shown below (changes in green) and thought it was working beautifully until I realized that all of my dates are 1 day later.  This is because the 3-digit number-of-days is being added to "01."  I have tried to resolve the problem, but I keep getting errors.  I am not M Code literate.  Any advice on how this can be amended?

 

Thanks!

 

Date.AddDays(Date.From(DateTimeZone.From("20" & Text.Start(Number.ToText([column reference]),4) & "-01-01")),Int64.From(Text.End(Number.ToText([column reference]),3)))

Is this correct? Consider January first (Julian 16001)  I think you would need to Minus 1 before your days add.

Sorry if thats a distraction. I found this post while looking for a more elegant conversion, as I already use something similar. something that includes dropping a day from the days add.

 

I did find a formula in DAX and M that works create in converting a 6 digit julian to a date. 

 

DAX

DATE(INT(BISalesView[Date Julian]/1000)+1900,1,MOD(BISalesView[Date Julian],1000))

 

M Language (Query Editor)

Date.AddDays(#date(Number.RoundDown([Date Julian]/1000)+1900,1,1),Number.Mod([Date Julian],1000)-1)

 

Hope this helps!

That was the simplification I was looking for @DarylM 

Thanks

Perfect, it works exactly how i needed. Thanks!

Thank you.

I was incorrect on something though in my example. the date is accually afive digit code (16343). I was not abel to modify your formula to work with the fivei digits, Also, I would need this to work in a table and column of many julian dates. Can I reference the column name rather than the Julian date in the forumla?

Yes you can reference a column name instead of a constant. It is what is recommended.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.