Forum Discussion

joshua1990's avatar
joshua1990
Post Prodigy
6 years ago

Convert Calendar Date into Julian Date

Hello everybody!

How can I convert a date like 01.01.2020 (DD.MM.YYYY) into 120001 with Power Query?

This is my approach with DAX

=
Var YearDay = 
Format(
    Datediff( 
        Date(   
             Year('Calendar'[Date]); 1; 1);
             'Calendar'[Date]; 
             Day) +1; 
    "0##")
Var Year=
Format('Calendar'[Date]; "1yy")

Return  
Year & YearDay
Need Help

1 Reply

  • artemus's avatar
    artemus
    Microsoft Employee
    6 years ago

    Here you go

    = let
   yearDate = Number.ToText(Duration.Days(DateTime.Date(DateTimeZone.LocalNow()) - #date(Date.Year(DateTime.LocalNow()), 1, 1)), "0##"),
   year = DateTime.ToText(DateTime.LocalNow(), "1yy")
in
   year & yearDate