Calculate amounts in local currency

[LisaB]

Hi,

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.

Any advice?

Contract table
Contract No	Currency Code
CON001	USD
CON002	EUR
CON003	SEK
CON004	GBP

Contract invoicing lines
Contract No	Item No	Line Amount
CON001	100	10 000
CON001	101	2 000
CON002	100	5 000
CON003	200	3 000
CON003	240	1 000
CON004	300	20 000

Currency exchange rates
2019-05-11	USD	10
2019-05-11	EUR	10
2019-05-11	SEK	1
2019-05-11	GBP	11
2019-05-12	USD	10,1
2019-05-12	EUR	9,9
2019-05-12	SEK	1
2019-05-12	GBP	11,2

Thanks!

Lisa B

[parry2k]

@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?

[LisaB]

Hi @parry2k,

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.

Expected result:

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.

Thanks.

[parry2k · Accepted solution]

@LisaB solution attached.

[LisaB]

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?

Many thanks

Lisa

[parry2k]

@LisaB any table name or column name changed?

[LisaB]

Hi,

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...

L

[LisaB]

Hi again @parry2k,

It seems like the formula doesn't pick up ResultIfFalse in the IF-statement, see picture:

[LisaB]

Hm - I hit tab ("1 ;") instead of  ";" directly after the number 1 and it now works again...

[LisaB]

Hi @parry2k ,

Many thanks - works like a charm.