Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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. 




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

View solution in original post


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?




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. 



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 


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

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors