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

[Arnoux]

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

[v-sihou-msft · Accepted solution]

@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]

Thank You so much, This worked well

[sguenther]

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