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!
