Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Also, we would like to differ between the latest exchange rate and a selected budget exchange rate:
Many thanks!
Don't you rather want to do that on transaction level?
I don't really follow - how would you do that and why?
Thanks
L
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
105 | |
87 | |
74 | |
66 |
User | Count |
---|---|
124 | |
112 | |
96 | |
80 | |
72 |