Summary
You can convert revenue data to a single currency, using formula fields.
This is valuable for companies that use multiple currencies, and want to analyse and act on data using a common currency denominator.
The formula takes the following inputs: "from amount", "from currency", "to currency" and "fx date"
"date" is optional
If your tenant uses historical fx rates, then the default is the latest rate if a date isn't specified
If your tenant doesn't use historical fx, then the formula uses the static rate saved in settings, regardless of whether a date is specified
Who is this article for?
All Planhat users
It's particularly relevant to those working with revenue - setting up filters, KPI dashboards, automations, et.c. that reference revenue data
Introduction
When working with revenue data in Planhat, you sometimes want to convert between different currencies.
For example, you might have licenses denominated in different currencies, based on the location of the customer. In order to report on, or filter, this data, you need first to convert all of the licenses to a common currency. This is what the formula field function “FX_CONVERT” is used for.
(The company model is an exception, since it already has system fields such as mrr, and arr which aggregate license data to the tenant's base currency, out of the box)
How FX Conversion works
FX_CONVERT accepts the following arguments:
[from amount], [from currency], [to currency], [fx date].
For example, if you imagine that you have a number of licenses denominated in different currencies, but your company “thinks” in USD as "base", then you can convert these licenses using a formula field that looks like this:
FX_CONVERT(<<arr>>, <<_currency>>, BASE, <<fromDate>>)
The above formula will take license arr, and the currency system field (“_currency”) and convert it to your tenant’s base currency (defined in settings), using the historical fx rate for the month of the license start date ("fromDate").
[from amount] will accept any number
[from currency] will accept any string or list value, provided it's a valid ISO currency code (e.g. "USD", "EUR", "GBP", et.c.)
[to currency] will accept any string or list value. It also accepts "BASE" which takes whatever is the base currency set in tenant settings
[fx date] will accept any ISO date. It will also accept "@today" as described above
All of the above arguments will accept a static value, as well as a dynamic reference (such as a field reference).
📌 Important to note
"date" is optional
If your tenant uses historical fx rates, then the default is the latest rate if a date isn't specified
If your tenant doesn't use historical fx, then the formula uses the static rate saved in settings, regardless of whether a date is specified
Cross-model formulas are not strictly supported
If, for instance, you want to convert all licenses to a single currency, and aggregate this data per company, you will first need an FX_CONVERT formula on the license model, and then a formula on the company model that aggregates data from the FX_CONVERT formula. In other words, you can't have an FX_CONVERT formula on the company model that directly fetches license data on the license model, but you can still achieve all of your objectives using more than one formula!
You must add currencies in settings, in order to use them in formulas
When using historical rates, if you are referencing a date in the future (for which there can not yet exist an fx rate), the default rate from settings is used until a rate exists for the specified month
For further details on adding currencies and how historical fx rates work, you can refer to our separate article