Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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,
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,
Thank You so much, This worked well
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |