of the
tl;dr
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:
| Date | 01-Jul-2020 | ||
| Exchange rate | 0.6895 | ||
| Name | Price | FX | Total USD |
| Router | $50 | 0.6895 | $34 |
| 5m cable | $30 | 0.6895 | $21 |
| Totals | $80 | 0.6895 | $55 |
How Dataverse calculates the totals:
- Take the price values entered by the user ($50 and $30), convert to the base currency ($34 and $21 USD)
- Calculate the total using base currency ($55)
- Convert back to $80 in the record’s currency (AUD) using FX rate of 0.6895
That was easy, right? Skip forward to November 2.
| Date | 02-Nov-2020 | ||
| Exchange rate | 0.7 | ||
| Name | Total | FX | Total USD |
| Router | $50 | 0.6895 | $34 |
| 5m cable | $30 | 0.6895 | $21 |
| Totals | $79 | 0.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:
| Date | 02-Nov-2020 | ||
| Exchange rate | 0.7 | ||
| Name | Total | FX | Total USD |
| Router | $50 | 0.6895 | $34 |
| 5m cable | $30 | 0.6895 | $21 |
| Firewall | $75 | 0.7000 | $53 |
| Totals | $154 | 0.7000 | $108 |
Note that the exchange rate for the last item is different. Roll forward to today:
| Date | 28-Jan-2021 | ||
| Exchange rate | 0.7628 | ||
| Name | Total | FX | Total USD |
| Router | $50 | 0.6895 | $34 |
| 5m cable | $30 | 0.6895 | $21 |
| Firewall | $75 | 0.7000 | $53 |
| Totals | $141 | 0.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 | $155 | 0.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.
Bottom line
Multi-currency aggregates rarely work the way the business wants out-of-the-box, handle them with care.
- Remember that any OOB aggregates are done in the base currency.
- Understand the individual records recalculate only when they are saved and they will use the current exchange rate (stored in the currency settings). That may or may not be what the business wants or expects.
- Explain the calculations to the business and confirm that is (or is NOT) expected and/or correct.
- Decide if the exchange rate should be updated on a regular basis using some external FX service OR if the business would prefer to have a fixed exchange rate and adjust it as required on an infrequent but regular basis (not an uncommon accounting approach).
- Apply UCS™ methodology[1] to figure out if custom calculations make sense. Most of the time they do.
[1] Use Common Sense