Hello, I searched and read the 2 posts about julian dating but I can't get a solution to work. I can either convert the julian date column on my inventory table YYDDD into a calendar date or add a column in my date table which would convert the date to the julian date. In the below example I need 17103 to be April 13 2017 or 4/13/17

Sorry, my mistake. Please use this instead Column = VAR myYear = 2000 + INT(DIVIDE([Julian Manufacture Date],1000)) VAR myDayOfYear = MOD([Julian Manufacture Date],1000) -1 RETURN DATE( myYear,1,1) + myDayOfYear

Julian Date Conversion or Format | Microsoft Fabric Community

15 Replies

Anonymous
Not applicable
6 years ago
I'm converting Julian dates in JD Edwards (Oracle DB), and I had to add "1900" to the year portion of the formula. So this is the working Power Query (M) formula I wound up with:

Date.AddDays( #date(Number.RoundDown([Julian Date]/1000)+1900, 1,1), Number.Mod([Julian Date], 1000)-1))
parry2k
Super User
8 years ago
well easier to add julian date in calendar table and set relationship on julian date between date and inventory table.

It can be handly to have in date table , in case you hve another table with julian dte, you don't need to convert it to date rather use julian date.
Phil_Seamark
Microsoft Employee
8 years ago
Hi jpt1228

THis might be close as a new calculated column

Column = VAR myYear = INT(DIVIDE([Julian Manufacture Date],1000)) VAR myDayOfYear = MOD([Julian Manufacture Date],1000) -1 RETURN DATE(Year(myYear),1,1) + myDayOfYear
- jpt1228
  Responsive Resident
  8 years ago
  Phil_Seamark
  
  Thanks - But getting an error when I add column into inventory table
  - Phil_Seamark
    Microsoft Employee
    8 years ago
    HI jpt1228
    
    I think you are missing the top line. You need the following.
    
    column =