Forum Discussion
LOOKUPVALUE returns blank value
Hi Paolo750f,
I modified your formula and it seems work on my side with your sample data, you can try to use below formula if it suitable for your requirement:
Week = LOOKUPVALUE(DateKey[Week],DateKey[Date],DATEVALUE(leads[new_enquirydate]))
Regards,
Xiaoxin Sheng
Hey Paolo750f, a couple questions here:
- Have you made sure that both the date columns are in the Date format?
- Is there a reason you need to use lookupvalue? If these two tables are related, you can use fields from both in the same chart. You could bring the Week value from the Date table on the chart, and then bring on the field from the Leads table to see them in the same chart.
I may not understand what you are trying to accomplish, so please feel free to correct my understanding!
Hi Jared,
Yes, both the date columns are in the same date format.
I was using lookupvalue following an article i found. I tried making the tables related and doing what you suggested but i don't get the result i'm expecting and if i try creating a new column using the RELATED expression the column remains blank.
What i'm trying to achieve is to be able to use the date table to filter my leads and opportunities by week (or any other range configured in the date table such as week of financial year, quarter, or financial year).
Strangely, if i create a new table with a column containing some dates and create a new column with lookupvalue to my date table it works correctly but whenever i try exactly the same using the data from CRM it doesn't return anything. I am wondering of there is something strange with my data as even if export from CRM to Excel and import that as a data source it does not work.
What i'm doing should work but for some reason it doesn't with my data set. I've even tried starting again and creating a new data set fromCRM and a new date table but get same result.
Paul
Hi Paolo750f,
Can you upload the pbix file and share us the link ?(1dr, google) I will test on it.
Regards,
Xiaoxin Sheng
Hi Xiaoxin,
i've cretaed a cutdown version of the file and put it here https://onedrive.live.com/?id=11887C660FAAD99%21205&cid=011887C660FAAD99
I've tested it and i see the same issues. I'd be interested to see if you find anything.
Regards,
Paul
Hi Paolo750f,
I modified your formula and it seems work on my side with your sample data, you can try to use below formula if it suitable for your requirement:
Week = LOOKUPVALUE(DateKey[Week],DateKey[Date],DATEVALUE(leads[new_enquirydate]))
Regards,
Xiaoxin Sheng