Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to 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 , 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 |
Thank you for your help! This worked easily with my data set.
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] )
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
75 | |
74 | |
55 | |
45 |
User | Count |
---|---|
117 | |
106 | |
77 | |
66 | |
65 |