2020 / 2019 Julian Date Issues

[noyesae]

I'm having an issue converting Julian Dates of 2020 and 2019.  I am currently using the below for a new column and it is working for Julian dates that start with 0 but for ones that start with 9 it's returning 2029 and I'm needing it to return 2019, how can I make this happen?  My dates are 4 digits, 9347, 0146, 0003, etc

Load Date = VAR myYear = 2020 + INT(DIVIDE([Julian Date],1000))

VAR myDayOfYear = MOD([Julian Date],1000) -1

RETURN DATE( myYear,1,1) + myDayOfYear

[lbendlin]

As far as I understand Julian dates are contiguous. Where does the 1000 come into play here?  Shouldn't you be using all digits of the Julian dates?

[noyesae]

I'm new to Power BI and I stole that formula doing a google search so I'm not sure where the 1000 is coming from or what it is doing.  I'm open to any suggestions on changes.

[HotChilli · Accepted solution]

The myYear variable is never going to return anything less than 2020.

If the year is either 2019 or 2020, then in the absence of anything more elegant:

VAR myYear = IF (INT(DIVIDE(TableJul[Column1],1000)) = 9, 2019, 2020)

[noyesae]

@HotChilli That just gets me the year, I need to turn 9001 to Jan 1, 2019 or 0001 to Jan 1 2020.

[HotChilli]

That's right.  You can do the substituting into your original formula.