[SOLVED] dated_exchange_rate formula for calculated property

[AJohnsson]

Edit: the hubspot support team helped me solve this. The formula should be written like this: [properties.amount___complete_solution] * dated_exchange_rate([properties.deal_currency_code],[properties.closedate])

This will convert the amount in my "Amount - Complete solution" property to the company currency from the selected currency on the deal with the exchange rate of the date in the "close date" property.

Hi,

I'm new to Hubspot's formulas and I'm trying to write a formula that multiplies the value in a property with the exchange rate (between the deal's selected currency and the company currency) and I can't get it to work.

My formula looks like this:

[properties.amount___complete_solution]/ dated_exchange_rate("Swedish Krona", [properties.closedate])

But the calculated property just shows the same exact amount as the properties.amount___complete_solution property. I've also tried writing it with "SEK" instead of "Swedish Krona" but then it just shows nothing at all. Swedish Krona is our company currency, so I thought maybe the dated_exchange_rate function just gives me the exchange rate between the company currency and the defined currency (which means it would always give me 1 if I set "Swedish Krona" in the function) but if I switched to Euro in the formula it still just calculated the same exact amount. 

Does anyone know what I'm doing wrong? Is it even possible to do what I'm trying to achieve?

[Ryan_VanStralen]

Actually I had the same issue and HubSpot help didn't have the answer outside a complicated workflow. Whether this is a new property or not, I came across just using the HubSpot property "exchange rate"  multiplied by whatever property you're converting.  The value of it is the deal currency exchange rate against your company currency.  So SEK would =1 for example, to USD =~ .09 .

For example, I wanted Annual Contract Value in Company Currency so the calculated property would look like the below.  

[jbeachamOBO]

Hey @AJohnsson! Do you know if currencies are properly configured in settings? Depending on your subscription type, you can use this feature to include multiple currencies on your deals, tracking what currency they were sold in and potentially eliminating the need for this formula column? 

[AJohnsson]

Yes we've got currencies configured, but I'm not interested in the amount of the total deal, just an amount of part of the deal (kind of like a line item, but not an actual line item). So I've got a custom currency property that's called "Amount - Complete solution" ("solution" being an internal phrase) that I need to convert to SEK whenever the deal is in another currency. Since hubspot doesn't allow conversion of custom currency fields in reports I thought maybe I could create a calculated property with a formula with the dated_exchange_rate function to convert the amount input into "Amount - Complete solution" to SEK.