# Dynamic Average Exchange Rate Lookup One or Multiple Months

#### a68tbird

##### New Member
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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### a68tbird

##### New Member
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.

Replies
26
Views
650
Replies
1
Views
346
Replies
0
Views
225
Replies
2
Views
465
Replies
5
Views
395

1,141,406
Messages
5,706,268
Members
421,437
Latest member
GijoeBlack

### 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?

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