Does anyone know how I can convert a Julian date (Example "116343") to a calendar date?
Struggling with a Dax formula for this.
Solved! Go to Solution.
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?
"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!
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?
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.