Dynamic Average Exchange Rate Lookup One or Multiple Months

a68tbird

New Member
Joined
Nov 15, 2011
Messages
26
Hello All -
I have a year-to-date sales sheet with total sales in four currencies: CAD, USD, GBP and EUR. The sales table has been loaded to the Data Model, and a relationship to the Calendar Table created so that specific month sales pivot table displays values according to a Timeline slicer. My CFO would like the report to convert all of the sales to CAD. I have loaded an Exchange Rate table to the workbook that will provide the closing rates for each month also loaded into the Data Model and related to the Calendar table. I'd like to be able to convert the sales in each currency to CAD according to the month selected in the timeline slicer, and where multiple months have been selected, to use the average across each month. So, essentially, timeline slicer has selected February 2021, sales pivot is displaying related sales per row in GBP, USD, CAD and EUR, and a second column would use the average exchange rates to convert to CAD. If I didn't have to worry about the month, I'd simply do an INDEX/MATCH or XLOOKUP, but want to incorporate the rate according to month or multiple months selected.

Thanks very much.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

a68tbird

New Member
Joined
Nov 15, 2011
Messages
26
For anyone interested, I managed to figure out a solution that works. I decided that it would be easiest to add a calculated column in my Data Model sales table that would lookup the relevant exchange rate. First, I needed to add a column to my sales table:
Power Query:
  = Table.AddColumn(#"Reordered Columns", "MonthBeginning", each Date.StartOfMonth([DatePaidGMT]))

Needed to add this as my exchange rates table only has monthly rates, not daily and without this, I was only getting a match when the sale was on the first of the month.

Then in the Data Model, in the sales table, I created a CAD Exchange column:
Excel Formula:
=IF([Currency] = "CAD",1,LOOKUPVALUE('Exchange Rates'[Rates],'Exchange Rates'[Currency],[Currency],'Exchange Rates'[Month],[MonthBeginning]))*[ShippingTotal]

My Exchange Rates table doesn't have CAD, so if my sales table returns values in CAD, I just need to multiply by 1, and if not I use a multiple criteria lookup: select the rate where both the currency and the month match, and then I can just multiply the sales total column by this returned rate.

So now, when I change the month with my timeline slicer, the pivot table returns the sum of the CAD exchanged values according to the rate in the month - or months - selected.

I'm sure there may be other more clever solutions, but this worked for me.

Cheers.
 
Solution

Forum statistics

Threads
1,141,070
Messages
5,704,112
Members
421,327
Latest member
Msh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top