3 Replies Latest reply on Jun 21, 2018 3:43 PM by Jon Boschee

    Different Yearly Exchange Rate Applied to Sales

    Jon Boschee

      Hi everyone,


      I'm trying to put together something where I can apply an exchange rate from 2018 to 2017 Sales and do this for other years.


      Anyone have any tips?



        • 1. Re: Different Yearly Exchange Rate Applied to Sales

          Hello Jon,


          Could you attach a sample workbook or some sample data?





          • 2. Re: Different Yearly Exchange Rate Applied to Sales
            Jim Dehner

            Hi Jon

            Would certainly like to see a workbook -

            Because you are talking about years and rates it sounds like you are looking at multiple countries and an extended period of time - and you want to convert local currency to a common currency (i.e. all the local values are being converted to the same common currency) and you are want to make the conversion at the point in time when the transaction took place?


            If so suggest you look at creating a rate conversion table with year and local currency (eg euros) as dimensions and the conversion rate as values - you would then join that table to your data based on year and local currency - you would then have a rate associated with each record that would allow you to convert the currency at the record level - it would allow you to aggregate / filter and compare the "common currency" at any level


            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

            1 of 1 people found this helpful
            • 3. Re: Different Yearly Exchange Rate Applied to Sales
              Jon Boschee

              Hi Jim,


              Unfortunately I can't share a workbook since it's work related.


              What I'm trying to do is to be able to convert back and forth between USD and local currency for gross sales with the larger goal of looking at 2017 gross sales at the 2016 exchange rate (for example).   While being able to flip between different years of gross sales and exchange rates.


              This is what I'm using for my formula to lump everything except USD into local currency so I only have two options in my filter:

              IF [Code] = "USD" THEN "USD"

              ELSE "LOCAL"



              (Code is the currency code such as EUR or GBP)


              Hope this makes sense.