Share
ReportI intend to create a new column in Dataverse where Resource_Price is fetched from Table1 for the resource and date in the current row in Table2 and use it to calculate the cost for this row in Table2.
The unique key in Table1 is a combination of the columns "Resource", "Startdate" and "Enddate", meaning that there will be duplicated records in the column "Resource" for different "Startdate" and "Enddate" (ResourcePrice will be different for different time periods):
| Resource | Startdate | Enddate | ResourcePrice |
| Resource1 | 20240101 | 20240531 | $1 000 |
| Resource1 | 20240601 | 20241231 | $2 000 |
| Resource2 | 20240101 | 20241231 | $1 500 |
My thinking is that a formula column using the LOOKUP-function might do the job, maybe something like:
LOOKUP(Table1; Table1[Resource] = Table2[Resource] &&
[Date] >= Table1 [Startdate] &&
[Date] <= Table1[Enddate])
[Date] >= Table1 [Startdate] &&
[Date] <= Table1[Enddate])
But I get an error message when I try to use the LOOKUP-function: "Lookup is an unknown or unsupported function".
Do you have suggestions on how to lookup as described?
Categories:
I have the same question (0)
All responses (
Answers (
946
