Forum Discussion

medwardsOT's avatar
medwardsOT
Regular Visitor
9 years ago
Solved

7 Digit Julian Date conversion

Hellowive looked for an answer regarding this but only ones regarding different julian forms.   I have a date field from our 400 server thats in 7 digit date format. Example: 2016005 That emaple ...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    If a query (M) solution is also fine, then you can add a custom column with the following code, in which "JulianDate" is your field with the 7-digit date:

     

    = Date.AddDays(#date(Number.RoundDown([JulianDate]/1000),1,1),Number.Mod([JulianDate],1000)-1)
Sean's avatar
Sean
Icon for Community Champion rankCommunity Champion
9 years ago

MarcelBeugThanks!

 

Maybe because it takes me 4 steps (3 conditional columns) and then in the 4th concatenate the final result to do this with DAX.

Split the number into year and the remaining 3 digits - Check if its a Leap Year

and then 2 long switch statements to convert the 3 digits - once into months and then - into days in each month

depending on whether it is a leap year or not

and then I concatenate and convert to Data Type: Date

 

But then again there may be an easier DAX solution

 

If anyone knows of one it may be OwenAuger or Anonymous or Anonymous or Vvelarde

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
9 years ago

Hi Sean

 

I would follow logic similar to MarcelBeug but slightly different:

 

CalendarDate =
DATE ( INT ( DIVIDE ( TheTable[JulianDate], 1000 ) ), 1, MOD ( TheTable[JulianDate], 1000 ) )

Or this version would be about the same as Marcel's.

CalendarDate =
DATE ( INT ( DIVIDE ( TheTable[JulianDate], 1000 ) ), 1, 1 )
    + MOD ( TheTable[JulianDate], 1000 )
    - 1

In this case, with the DATE function in DAX (and Excel for that matter) you can get away without explicitly working out the months/days.

It will automatically "roll over" the years/months as required if either the month argument is outside 1..12 or the day argument is outside the date range of the month. 

 

For example DATE(2016, 1, 100) is equivalent to DATE(2016, 4, 9), i.e. the 100th day counting from 1st Jan 2016.

 

Cheers,

Owen :)

  • Sean's avatar
    Sean
    Icon for Community Champion rankCommunity Champion
    9 years ago
    OwenAuger wrote:

    For example DATE(2016, 1, 100) is equivalent to DATE(2016, 9, 4), i.e. the 100th day counting from 1st Jan 2016.


     

    As always OwenAuger - that works great! Thank You! :smileyhappy:

     

    Indeed the DATE function takes care of this I did not know/remember this!

     

    the 100th day in 2016 - April 9, 2016 - DATE ( 2016, 1, 100 )

    the 100th day in 2015 - April 10, 2015 - DATE ( 2015, 1, 100 )

     

    Thanks Owen!

    Sean