crmtipoftheday: Tip #1396: Multi-currency aggregates in Dataverse
When using aggregates with multiple currencies make sure the business understands how the totals are calculated, how exchange rate is used, and why it’s a good idea to roll out your own calculations.
Bring it on
Multi-currency support has always been one of the staple features of …. eerrr … Dataverse since version … uhm … 4.0? And it’s a great one out of the box: enter the value, specify the currency, and kaboom, it’s converted on the fly using whatever the current exchange rate is, and saved in both base and record’s currencies.
Where it starts getting complicated when you consider changes over the time and aggregates. Time component affects the calculations because the exchange rate changes. Aggregates throw in another spanner because you can’t simply add Icelandic Krónas and Netherlands Antillean Guilders.
Let’s say we have sites around the globe with some equipment purchased and assigned. Our base currency is USD. Site total are calculated using rollup fields.
Site record for Sydney, Australia (not Sydney, Nova Scotia) as of 1 July 2020:
Date01-Jul-2020Exchange rate0.6895NamePriceFXTotal USDRouter$500.6895$345m cable$300.6895$21Totals$800.6895$55 How Dataverse calculates the totals:
Date02-Nov-2020Exchange rate0.7NameTotalFXTotal USDRouter$500.6895$345m cable$300.6895$21Totals$790.7000$55 Same data but we just lost $1 because the exchange rate went up to 0.7. Individual items didn’t recalculate as we haven’t change anything.
Let’s keep going. On the same date we added another item:
Date02-Nov-2020Exchange rate0.7NameTotalFXTotal USDRouter$500.6895$345m cable$300.6895$21Firewall$750.7000$53Totals$1540.7000$108 Note that the exchange rate for the last item is different. Roll forward to today:
Date28-Jan-2021Exchange rate0.7628NameTotalFXTotal USDRouter$500.6895$345m cable$300.6895$21Firewall$750.7000$53Totals$1410.7628$108 And we seemingly lost money again because our peso keeps climbing.
While math is correct, it does not feel right. We purchased items in AUD so the total shouldn’t change. Right? The answer is “it depends” on how your business records the value of the assets, how the depreciation is calculated, and if the moon is in the house of Saturn. We’re not even taking into account when the individual lines will recalculate – it’s a separate topic.
The most common localized style of recording is to calculate the aggregate value in the local currency and then convert to USD. Total line should look like this:
Totals$1550.7628$118 Things can get really heavy if individual items can indeed be in the different currencies. You’d need to decide if you want to convert the items to the base or local currency when doing the calculations.
That makes more sense but it’s not possible for Dataverse to perform this mental gymnastics. Solution? Do it yourself. It wouldn’t be too difficult to write a cloud flow that would iterate over the assets and calculate the total value the way the business wants. Needs to be real-time? Summon a developer and get them to write a plugin.
Multi-currency aggregates rarely work the way the business wants out-of-the-box, handle them with care.
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.
|crmtipoftheday: Tip #1321: Formatting currency values in Power Apps portals||Blog bot||Dynamics CRM: Blogs||0||10.02.2020 18:11|
|crmtipoftheday: Tip #1279: Select your region in multi-geo deployments||Blog bot||Dynamics CRM: Blogs||0||26.06.2019 15:11|
|crmtipoftheday: Tip #1097: Change chart labels on currency values to show in Millions or Thousands||Blog bot||Dynamics CRM: Blogs||0||04.04.2018 08:11|
|crmtipoftheday: Tip #1063: Do not touch currency system views||Blog bot||Dynamics CRM: Blogs||0||29.01.2018 04:21|
|dynamicsax-fico: Foreign currency revaluation||Blog bot||DAX Blogs||0||14.05.2016 00:16|
|Опции темы||Поиск в этой теме|