Forum Discussion

Arnoux's avatar
Arnoux
Frequent Visitor
9 years ago
Solved

Date conversion issue from datenum with a few 0's to date

Hi I ran this formula to extract dates from another table so that i can use that in my datediff function,   Itinerary Created Date = IFERROR(LOOKUPVALUE(Itinerary[Created_DT];Itinerary[Itinerary_C...
Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    Arnoux

     

    In this scenario, your lookupvalue() function returns a numeric value, I assume it looks like "20160101", this format can be recognized as date when changing its data type. You need to split the number and concatenate it into a date string, then you can convert it into a date in Power BI Desktop or using DATEVALUE() to convert it.

     

    DateColumn = 
var YearPart=LEFT(Table5[Column1],4) 
var MonthPart=LEFT(RIGHT(Table5[Column1],2),2)
var DayPart=RIGHT(Table5[Column1],2)
return
DATEVALUE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(YearPart,"/"),MonthPart),"/"),DayPart))

     

    Regards,

v-sihou-msft's avatar
v-sihou-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Arnoux

 

In this scenario, your lookupvalue() function returns a numeric value, I assume it looks like "20160101", this format can be recognized as date when changing its data type. You need to split the number and concatenate it into a date string, then you can convert it into a date in Power BI Desktop or using DATEVALUE() to convert it.

 

DateColumn = 
var YearPart=LEFT(Table5[Column1],4) 
var MonthPart=LEFT(RIGHT(Table5[Column1],2),2)
var DayPart=RIGHT(Table5[Column1],2)
return
DATEVALUE(CONCATENATE(CONCATENATE(CONCATENATE(CONCATENATE(YearPart,"/"),MonthPart),"/"),DayPart))

 

Regards,

Arnoux's avatar
Arnoux
Frequent Visitor
9 years ago

Thank You so much, This worked well