Could you attach a sample workbook or some sample data?
1 of 1 people found this helpful
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.
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"
(Code is the currency code such as EUR or GBP)
Hope this makes sense.