Currency conversion with dates from Salesforce Data

[Anonymous]

Hi!

I'm new in Power BI, and need some help with an issue with Salesforce data. The salesforce automaticaly convert the opportunity's amount to the current currency (in this case BRL), but when I got the data in Power BI, I don't see the amount converted, need to calculate between two tables (one with the amount, the currency and the close date, and other with the currency, the start date, the end date and the conversion rate), just like the images on links below.

https://ibb.co/FXmqxBm

https://ibb.co/HKK3F6G

https://ibb.co/hHVPp9t

How can I create a new colunm with the amount converted in the correct date? I know how to do this in Excel form (=@D:D(amount)/SE(@C:C(currency in the first table)="BRL";1;SOMASES(Planilha2!D:D(conversion rate);Planilha2!A:A(currency in table 2;@Planilha1!C:C(currency in table 1);Planilha2!B:B(start date);"<"&@Planilha1!E:E(close date);Planilha2!C:C(end date);">"&@Planilha1!E:E(close date)))

how can I use this logic in DAX?

Thank you,

Rebecca

[Anonymous · Accepted solution]

In case anyone look for this answer, I got it...

Amount converted = CALCULATE(sum(Oportunidade[ValorTotalContrato__c])/
SUM('Taxa de conversão datada'[ConversionRate]);
FILTER('Taxa de conversão datada';Oportunidade[CurrencyIsoCode]='Taxa de conversão datada'[IsoCode]);
FILTER('Taxa de conversão datada';Oportunidade[CloseDate]>='Taxa de conversão datada'[StartDate]&&Oportunidade[CloseDate]<'Taxa de conversão datada'[NextStartDate]))

🙂

[omar___sherif]

Ok, so this is working, but whenever i try to perform any operation or use this 'Amount Converted' field in any other dax expression i get ' a circular dependancy was detected' error.

Any idea how to solve this issue?

Thanks