cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
MVP

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

8 REPLIES 8
MVP

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

Hope you can use it for something

New Member

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)))

Resolver II

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.

Helper II

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)

Hope this helps!

Resolver II

That was the simplification I was looking for @DarylM

Thanks

Perfect, it works exactly how i needed. Thanks!
Helper II

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?

MVP

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

Announcements