A currency column in Dataverse allows you to store monetary values in different currencies. It also creates a base column that converts the values to a single currency using exchange rates. You can use this feature to perform financial transactions and analysis across multiple currencies.
But how does it work and what can it do?
Currencies
Each environment within the Power Platform is associated with its own currency. When you begin working in an environment that uses currency, you will be prompted to select your default (base) currency. This choice determines the currency in which your environment will operate in the future.
Create a new Currency
Once you’ve established your base currency, you can add multiple other currencies. Let’s create one now, and I’ll guide you through the process step by step:
- Go to the Power Platform Admin Center.
- Select the environment for which you want to create additional currencies.
- Click on „Settings.“
- Then, go to „Business.“
- Choose „Currencies.“
- At the top left, select „New.“
In the new menu, you’ll find the option to choose from 221 predefined currencies. To select one of these currencies, follow these steps:
- In the Currency Code field, click on the small icon located on the right side.
- A list of predefined currencies will appear. Choose the currency you want to add.
- Once you’ve selected a currency, the remaining fields will automatically be filled with predefined values.
When adding a new currency, you’ll notice that all the fields are automatically populated except for one – the Exchange Rate. You’ll need to input the exchange rate manually. For your initial setup, you can easily find the current exchange rate with a quick online search, such as using Google. I’ll be covering how to automate this process in an upcoming article.
After you’ve entered the necessary details, click „Save and Close.“ Repeat these steps for as many currencies as your environment requires.
But where does the magic happen?
As previously mentioned, when you create a Currency Column in Dataverse, it automatically generates two additional columns. For instance:
- Loan (Base): This column stores the Loan amount in the default currency of your environment.
- Loan: The Loan column stores the amount in the currency you’ve selected as your default in the Personalization Settings.
- Exchange Rate: This column reflects the exchange rate you’ve configured in the admin panel. It specifies the conversion rate between the User Default Currency and the Environment Default Currency.
For example, if you’ve chosen the Canadian Dollar as your personalized currency, even though the environment’s default currency is Euro, these columns allow you to work with and convert between these currencies seamlessly. Example:
We saved 55000 CAD but let us look which values the other two columns have.
Even if you’ve saved $55,000, the Base (Basis) Value is the converted amount, which is the value you set as the exchange rate during the currency’s creation.
It’s important to note that once a row is created with a specific currency type, you cannot change that type.
Conclusion
In summary, there are three types of columns:
- The created one: This column contains the amount in your default currency, which you selected in your settings.
- The Base Column of the created one: This column stores the value in the environment’s default currency.
- The Exchange Rate: This column calculates the difference between the environment’s default currency and the personalized value you’ve chosen.