How to Convert a Julian Date to Calendar Date In Power BI

How to Convert a Julian Date to Calendar Date (vice versa) using Power Query in Power BI
Scenario:
Suppose we have Calendar Dates as follows.

here, the DimDate is the Date Dimension, which is the Normal Calendar Date.

1) Converting the Calendar Date to Julian Date:
We can use the below Power Query (as a new Custom Column) to convert the Calendar Date to Julian Date.

Convert2Julian

= Table.AddColumn(DayOfMonth, "JulianDate", each 

let

   YrDate = Number.ToText(Duration.Days(DateTime.Date([DimDate]) - #date(Date.Year([DimDate]), 1, 1))+1, "0##"),  

Yr = Date.ToText([DimDate], "1yy"),

   JulianDt = (Yr & YrDate)

in

   JulianDt

)

Result:

Notes:
The Julian Date for the Calendar Date : 1/10/2024 ( 2024-01-10) is 124010.

In the Julian Date 124010 (can read as 1-24-010), the '1' represents a Century, '24' represents the 2 digits of a Year (2024), and '010' represents the Day of the Year.

The exact Juian Date for the Calendar Date : 1/10/2024 ( 2024-01-10) is 24010.

We can use the following DAX Formula to covert a Julian Date to Calendar Date:

MyCalDate =

DATE(INT(MyTable[JulianDate]/1000)+1900,1,MOD(MyTable[JulianDate],1000))

2) Converting the Julian Date to Calendar Date:

We can use the below Power Query (as a new Custom Column) to convert the Julian Date to Calendar Date.

Convert2Date

= Table.AddColumn(#"Changed Type", "CalendarDate", each Date.AddDays(#date(Number.RoundDown([JulianDate]/1000)+1900,1,1),Number.Mod([JulianDate],1000)-1)

)

Result:

Notes:

Make sure, the format of the Julian Date is in the Number before converting to Calendar Date.

The Complete Power Query for reference:

let

/* The Calendar Table with Dynamic Dates based on Today Date */

vStartDate = Date.StartOfYear(Date.AddYears(Date.From(DateTime.LocalNow()),1)),

vEndDate = Date.EndOfYear(Date.AddYears(Date.From(DateTime.LocalNow()),1)),

DatesList = 

    List.Dates(vStartDate, Duration.Days(vEndDate-vStartDate)+1, #duration(1,0,0,0)),

DatesTable = 

    Table.FromList(DatesList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

DimDate = 

    Table.RenameColumns(DatesTable,{{"Column1", "DimDate"}}),

DimYear = 

    Table.AddColumn(DimDate, "DimYear", each Date.Year([DimDate])),

MonthNo = 

    Table.AddColumn(DimYear, "MonthNo", each Date.Month([DimDate])),

DayOfMonth = 

    Table.AddColumn(MonthNo, "DayOfMonth", each Date.Day([DimDate])),

    Convert2Julian = Table.AddColumn(DayOfMonth, "JulianDate", 

    each let

    YrDate = Number.ToText(Duration.Days(DateTime.Date([DimDate]) - #date(Date.Year([DimDate]), 1, 1))+1, "0##"),

    Yr = Date.ToText([DimDate], "1yy"),

    JulianDt = (Yr & YrDate)

    in

   JulianDt),

    #"Changed Type" = Table.TransformColumnTypes(Convert2Julian,{{"DimDate", type date}, {"DimYear", Int64.Type}, {"JulianDate", Int64.Type}}),

    Convert2Date = Table.AddColumn(#"Changed Type", "CalendarDate", each Date.AddDays(#date(Number.RoundDown([JulianDate]/1000)+1900,1,1),Number.Mod([JulianDate],1000)-1))

in 

Convert2Date

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------