Dynamic Average Exchange Rate Lookup One or Multiple Months

[a68tbird]

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.

 

[a68tbird]

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:

  = 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:

=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.