Dynamic currency exchange based on currency selected by the user

[Zosy]

Hi,

I am trying to build a dashboard where the user would choose the currency for the sales from a slicer. Based on this selection, the base amount of the invoices expressed in SEK would get converted to their specified currency using the corresponding conversion rates (by year and currency).

If the end user would choose EUR from the slicer, in 2022 the amount should be  1500 SEK * 0.6744 = 1,011.6 EUR  and so on. How can I achive it?

Desired outcome:

Year	Total Sales
2019	809.28
2020	1686
2021	1449.96
2022	1011.6

DATA USED

Invoices

Invoice Number	Date	Amount (SEK)
1	15-Sep-2021	100
2	25-Jan-2019	200
3	6-Jan-2019	500
4	22-Mar-2020	900
5	22-Apr-2022	300
6	15-Sep-2022	800
7	25-Jan-2020	1300
8	7-Jan-2019	500
9	20-Mar-2021	2000
10	10-Apr-2022	400
11	15-Sep-2020	300
12	20-Jan-2021	50

Currency Rates

exchangerate	year	currency_name	currency_code
0.6535	2019	Euro	EUR
0.5781	2019	Pound Sterling	GBP
0.7718	2019	US Dollar	USD
0.5901	2020	Pound Sterling	GBP
0.6731	2020	Euro	EUR
0.7536	2020	US Dollar	USD
0.6538	2021	Euro	EUR
0.7455	2021	US Dollar	USD
0.5814	2021	Pound Sterling	GBP
0.6744	2022	Euro	EUR
0.5798	2022	Pound Sterling	GBP
0.7978	2022	US Dollar	USD

Thank you.

[Anonymous · Accepted solution]

@Zosy , this can be done by creating these two measures:

Total Amount (SEK) = SUM('Invoices'[Amount (SEK)])

Total Sales (in selected currency) = 
    SUMX(
        VALUES('Invoices'[Date].[Year]),
        VAR vExchangeRate = 
            LOOKUPVALUE(
                'CurrencyRates'[exchangerate],
                CurrencyRates[currency_code], SELECTEDVALUE('CurrencyRates'[currency_code]),
                CurrencyRates[year], 'Invoices'[Date].[YEAR]
            )
        RETURN
            [Total Amount (SEK)] * vExchangeRate
    )

This gives me these results:

Year	Total Sales (in selected currency)	currency_code
2019	784.2	EUR
2019	693.72	GBP
2019	926.16	USD
2020	1682.75	EUR
2020	1475.25	GBP
2020	1884	USD
2021	1405.67	EUR
2021	1250.01	GBP
2021	1602.825	USD
2022	1011.6	EUR
2022	869.7	GBP
2022	1196.7	USD

[Zosy]

Thank you for your help! This worked easily with my data set.

[tamerj1]

Hi @Zosy 

best and easiest method is to create a date table that contains the date and the year. Connect the date with the invoiced table and connect the year with currency exchange table. Then you measure would be

SUM ( Invoices[Amount (SEK)] ) * SELECTEVALUE ( 'Currency Rates'[exchangerate] )