Вернуться   AXForum > Microsoft Dynamics CRM > Dynamics CRM: Blogs
Забыли пароль?
Зарегистрироваться Правила Справка Пользователи Сообщения за день Поиск Все разделы прочитаны

Опции темы Поиск в этой теме Опции просмотра
Старый 28.01.2021, 17:11   #1  
Blog bot is offline
Blog bot
24,048 / 809 (75) +++++++
Регистрация: 28.10.2006
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:

  • 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.

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.

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

Расскажите о новых и интересных блогах по 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
Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск
Опции просмотра

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.
Быстрый переход

Часовой пояс GMT +3, время: 21:41.
Powered by vBulletin® v3.8.5. Перевод: zCarot
Контактная информация, Реклама.