I have different amounts in currency but wish to calculate them to local currency.
I have three different tables, see examples below. The first table contains the currency code but no amounts. The second table (invoicing lines) does not contain currency codes but line amounts. The third table is the exchange rate table that contains one exchange rate per day.
In the second table, the invoicing lines, I only want to retreive some amounts, I have marked them in bold.
I wish to retreive the amounts in local currency for only some invoicing lines (this will be a page level filter) using the latest exchange rate available.
|Contract No||Currency Code|
|Contract invoicing lines|
|Contract No||Item No||Line Amount|
|Currency exchange rates|
Solved! Go to Solution.
@LisaB your post in not very clear:
what is local currency?
You mentioned for few line items you want to convert? What is the logic of few lines?
Can you put example of what you expected result to be?
Also I believe contact number in contract table (1st table) is unique?
The local currency is SEK (exchange rate = 1)
The first table is the contract header where contract no is the unique key. The second table is the invoicing lines for the contracts. The purpose is to calculate the ARR and only a few items numbers are recurring revenue. However, as long as I can get the amount i SEK for each line I can put a page level filter on item number.
|Contract No||Item No||Line Amount||Currency code (not in table)||Line Amount (LCY) (not in table)|
|CON001||100||10 000||USD||101 000|
|CON001||101||2 000||USD||20 200|
|CON002||100||5 000||EUR||49 500|
|CON003||200||3 000||SEK||3 000|
|CON003||240||1 000||SEK||1 000|
|CON004||300||20 000||GBP||224 000|
Hope this clarifies.
Hi @parry2k ,
The solution you sent worked perfectly fine until now (?). This is how the formula now looks like. I haven't made any changes to the data or the formula.
Can you see why I get this error?
Nope, no changes. What's wierd is that I can refresh the report without any problems, it is when I look into the formula I get the error and after that it is no longer possible to refresh the report. Hope that makes sense...
Learn how to create your own user groups today!
Click here to read more about the October 2021 Release!
Check out the new Power Platform Community Connections gallery!
Mark your calendars and join us for our next Power BI Dev Camp!