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_Code_CH];Fact_Sales[Itinerary_code_ch]);0)

However, it gave me circular dependacy issues and now i had to write an if error around for those few cases.

 

Now my other issue is that it returns numbers and as soon as i try and change the date format for my datediff formulat to be able to work, it gives me this error

 

Can anyone please help me to figure out how i can get pass this

 

Thanks

Arnoux

 

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,

3 Replies

  • sguenther's avatar
    sguenther
    Advocate II
    9 years ago

    Probably the 0, which gets returned by your Iferror function can not be converted to a date. Try returning BLANK() instead and check again. I think this should fix it.

     

    Itinerary Created Date = IFERROR(LOOKUPVALUE(Itinerary[Created_DT];Itinerary[Itinerary_Code_CH];Fact_Sales[Itinerary_code_ch]);BLANK())

    Best,

    Sebastian

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft 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