0

I have two formula fields: one on the Opportunity and one on the OpportunityLineItem that are calculating the exchange rates to USD:

CASE( CurrencyIsoCode ,

"EUR", Amount * 1.11,
"GBP", Amount * 1.2,
"HKD", Amount * 0.11,
"USD", Amount,

0)

The formulas are working perfectly fine, but I need them to keep the exchange rates for each FY. I know I can create a new field for 2021 exchange rate, but I would like to keep the same formulas for the user experience and best practices.

I was looking to use CreatedDate ">=1/01/2020" and "<=31/12/2020", but I am unable to save the formula now.

Any help is much appreciated. Thanks.

1

Have you looked into enabling Multiple Currencies and then turning on Advanced Currency Management in order to use date-range enabled exchange rates?

There are implications, for example you cannot undo it once you've enabled multiple currencies and there are a whole host of things you want to be familiar with before you turn it on.

But in terms of best practice, it's always a good idea to check out the native functionality before you try to roll your own.

....

One approach to doing what you want to do if the native functionality doesn't support it is forget about the formula fields and do this off an Apex Trigger or invoke an apex method from Flow or Process Builder, in conjunction with a Custom Metadata Type or a query to the DatedConversionRate object (that is available after you've turned on Multiple Currencies and Advanced Currency Management). If the DatedConversionRate object has all the records you need to satisfy your requirements, then use that. Otherwise roll your own with Custom Metadata.

Then your apex method would call the CMDT or query the conversion rate, constrained by the date the Opportunity was created (and currency of course) so that it would always pull the right conversion rate for that Opportunity. Then update your Currency field after having done the conversion.

Trigger it off a change to the record where the amount field the calculation is based on is altered.

Thinking about it - you can probably do all of this in a Flow without having to do the invokable apex method bit...

4
  • Hi, Thanks for your reply. I do have multiple currencies enabled, but unfortunately, I have two sales teams using the same instance - and they have different exchange rates they are reporting on. For one of the teams USD is the corporate currency and for the other team is GBP. This is the reason why I have created this formula field.
    – SSimion
    Mar 6 '21 at 11:57
  • 1
    Why don't you set the primary currency at the record level; so that for one team it is USD and the other team it is GBP? I thought you can do that.
    – edralph
    Mar 6 '21 at 12:58
  • 1
    ...and each User can set their primary currency, so when they create leads/opportunities etc it will default the record's primary currency to their currency. I'm guessing there is still something about this that doesn't work for you, otherwise you would be doing it!
    – edralph
    Mar 6 '21 at 13:05
  • Correct. One of the teams that have the GBP as corporate, it's fine as I can add the exchange rates in the Currency Setup and regardless of the currency they are selecting at the record level, everything gets converted to those rates. For the other team instead, regardless of the currency at record level, their Opportunities always need to be converted to USD and they have completely different exchange rates than the rest. Also, they would like to keep the historic exchange rates for 2020 and have the new 2021 exchange rates implemented, if that makes more sense now
    – SSimion
    Mar 6 '21 at 15:31
0

The specific answer to the formula question you have is:

You have:

CreatedDate ">=1/01/2020" and "<=31/12/2020"

this is not a valid formula expression

You'll want something like this:

CASE(YEAR(DATEVALUE(CreatedDate)),
     2020,CASE(CurrencyIsoCode,
               'EUR',1.11,
               ...), 
     2021,CASE(CurrencyIsoCode,
               'EUR',1.13,
               ...),
     2022,CASE(CurrencyIsoCode,
               'EUR',1.17,
               ...),
     ...
 ) 

That said, I'd use Opportunity.CloseDate rather than CreatedDate as typically these exchange rates matter only for closed won business and in the year when they close, not the year when the opportunity was created.

P.S. I feel sorry for your finance team that has to manage multiple (different) exchange rates for the same currency for the same corporate entity. What a nightmare.

3
  • yep or hard code the rates into the formula like this. p.s. most businesses of any decent size that get revenue internationally have to do management reporting with 'fixed exchange rates' so that exchange rate fluctuations don't distract from the commercial trends. But they need to be changed every year to reflect some semblance of reality.
    – edralph
    Mar 6 '21 at 18:49
  • 1
    @edralph - i have no issue w/ annual exchange rates; it is when BU X uses different exchange rates than BU Y for the same currency C that my eyebrows raise.
    – cropredy
    Mar 6 '21 at 19:04
  • agreed! that is a bit mad.
    – edralph
    Mar 6 '21 at 19:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.