LOOKUPVALUE or another formula?

[Anonymous]

Good day,

I am trying to create a caluclated column, which pulls in a value "conversion rate" from a currency conversion rate table, based on a specified date, and the currency code (Cdn, AUD etc). There is no link between the two tables, as they do not share any unique identifiers.

In order for the correct conversion rate to be pulled, the "Close Date" from the original Opportunity table has to fall within a range "Start Date" - "Next Start Date" in the currency table. In addition, the Currency ISO codes between the two tables must also match. Here is the syntax that I tried, but it returns an error: A table of multiple values was supplied where a single value was expected. 

Being new to Power BI, I am not sure if this is even the correct approach? Any advice would be appreciated!

//

Conversion Rate = if(and('Opportunity Product_ACVe'[Close Date]>=related('Dated Conversion Rate'[StartDate]),'Opportunity Product_ACVe'[Close Date]<=related('Dated Conversion Rate'[NextStartDate])),(LOOKUPVALUE('Dated Conversion Rate'[ConversionRate],'Dated Conversion Rate'[IsoCode],'Opportunity Product_ACVe'[CurrencyIsoCode])),0)

//

[v-shex-msft]

Hi @Anonymous,

Based on error message, it seems like you formula can find out multiple records based on above conditions.
Current calculated column and measures not support stored multiple records, I think you need to modify your formula to cut down the result amount or use CONCATENATE function to merge them to string.

Can you please share some sample data to test? it will be help for troubleshooting your scenario.

Regards,

Xiaoxin Sheng

[Anonymous]

Good day

I am trying to pull the correct currency exchange rate given a specific date that falls under a date range in the destination table, and a specific currency code. The two tables are not related. This is the syntax I tried, but it returns an error: A table of multiple values was supplied where a single value was expected.

Any help would be greatly appreciated!

Conversion Rate = if(and('Opportunity Product_ACVe'[Close Date]>=related('Dated Conversion Rate'[StartDate]),'Opportunity Product_ACVe'[Close Date]<=related('Dated Conversion Rate'[NextStartDate])),(LOOKUPVALUE('Dated Conversion Rate'[ConversionRate],'Dated Conversion Rate'[IsoCode],'Opportunity Product_ACVe'[CurrencyIsoCode])),0)