Exchange rates over time - model attached

[carlenb]

Hi all,

See model with data here: https://drive.google.com/file/d/1C19jmtujw53LD2YPwvLDq1uF_QLxRIu5/view?usp=sharing

I have the following data

• Sales table with [sales], [currency], [order date] from 2019-2022 
• Exchange rate table with [from currency], [to currency] [exchange rate] and [valid from] 2019-2022 

Problem

• I want to sum the sales from 2019-2022 and convert to local currency (SEK)
• For example if the exchange rate from EUR to SEK is 8,6 then a sales order of 10 euro would convert to 86 SEK
• Sales order date must pick the "Valid from" exchange rate closest to this date
• Example: Based on the data tables below, the USD sales order 2019-05-02 should pick the exchange rate from 2019-06-07, the exchange rate is 9,5 and sale in local currency 15 656 

Sales volume	Sales date	Currency
1880	2019-04-01	SEK
1648	2019-05-02	USD
906	2019-06-03	EUR
1071	2019-07-04	EUR
1577	2019-07-04	EUR
749	2020-04-01	EUR
1085	2020-04-02	EUR
1061	2020-08-03	USD
1885	2020-04-04	SEK
649	2020-04-05	CHF
287	2021-04-01	CHF
1211	2021-04-15	SEK
1365	2021-04-03	EUR
755	2021-04-04	SEK
1908	2021-04-05	USD
1276	2021-04-06	USD
1654	2022-04-14	SEK
1993	2022-04-08	CHF
1697	2022-04-09	CHF
1928	2022-10-10	SEK
506	2022-04-11	EUR
985	2022-04-12	SEK
1951	2022-04-13	SEK
1327	2022-04-14	SEK

From currency	To currency	Valid from	Exchange rate
CHF	SEK	2019-05-06	12,1
USD	SEK	2019-06-07	9,5
EUR	SEK	2019-07-05	8,6
CHF	SEK	2019-08-06	12,11
USD	SEK	2019-09-07	9,57
EUR	SEK	2019-10-05	8,3
CHF	SEK	2020-05-06	12,1
USD	SEK	2020-06-07	9,5
EUR	SEK	2020-07-05	8,6
CHF	SEK	2020-08-06	12,11
USD	SEK	2020-09-07	9,57
EUR	SEK	2020-10-05	8,3
CHF	SEK	2021-05-06	12,1
USD	SEK	2021-06-07	9,5
EUR	SEK	2021-07-05	8,6
CHF	SEK	2021-08-06	12,11
USD	SEK	2021-09-07	9,57
EUR	SEK	2021-10-05	8,3
CHF	SEK	2022-05-06	12,1
USD	SEK	2022-06-07	9,5
EUR	SEK	2022-07-05	8,6
CHF	SEK	2022-08-06	12,11
USD	SEK	2022-09-07	9,57
EUR	SEK	2022-10-05	8,3

Output

• A measure where the 2019-2022 sales are converted to SEK based on the sales table
• From this I can create a graph with Sales (y axis) and Year (x-axis)

[carlenb · Accepted solution]

I solved this myself by importing a historial exchange rate table from investing.com (with ALL dates ranging from 2019 to 2022) and then following this guide: 

https://www.youtube.com/watch?v=9oPFgHvGyKc&t=14s