Multi currency report - change currency/exchange rate based on enitity

[LisaB]

Hi everyone,

I have three different reports that I would like to merge into one report. Every report extracts data from one single company in Microsoft Dynamics 365 Business Central, the merged report will extract data from three companies.

1st report = Swedish entity, LCY = SEK

2nd report = Danish entity, LCY = DKK

3rd report = Australian entity, LCY = AUD

The reports show both values in currency and in LCY, using this formula to get the latest exchange rate (this is from the Swedish report):

Exchange Rate =
VAR recentDate =
CALCULATE( MAX( 'ExchangeRates'[Starting_Date] ), 'ExchangeRates'[Currency_Code] = EARLIER( 'ILC_Invoicing_Lines'[Currency_Code] ))
RETURN
IF( 'ILC_Invoicing_Lines'[Currency_Code] = "SEK", 1 ,
CALCULATE( MAX( 'ExchangeRates'[Exchange_Rate_Amount] ), 'ExchangeRates'[Currency_Code] = EARLIER( 'ILC_Invoicing_Lines'[Currency_Code] ),
'ExchangeRates'[Starting_Date] = recentDate ))

Example data:

	Amount (currency)	Currency code	Company
SE-001	100	SEK	SE
SE-002	150	USD	SE
DK-001	200	DKK	DK
DK-002	500	SEK	DK
AU-001	100	AUD	AU
AU-002	200	EUR	AU

The exchange rate table will be based on SEK.

What I would to achieve:

• Link a company/entity to a LCY.
• Based on chosen currency code in a filter panel, set that currency code as LCY for the report and show values calculated to LCY.
• If possible, based on chosen company, automatically choose the LCY for that company as currency code.

Also, we would like to differ between the latest exchange rate and a selected budget exchange rate:

• How do we enter the budgeted exchange rate?
• How can we switch between budgeted exchange rate and latest exchange rate?

Many thanks!

[lbendlin]

• Link a company/entity to a LCY.

Don't you rather want to do that on transaction level?

[LisaB]

I don't really follow - how would you do that and why?

Thanks

L

[lbendlin]

In our industry customers can conduct one transaction in SEK and the next one in USD.  It's their choice, and we record the base currency of the transaction rather than assuming that the customer always transacts in SEK (for example)