Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I would like to add a column to a table that always shows the amount in euro. My table looks like this (example):
Yearmonth | Amount | Currency |
202001 | 48567 | EUR |
202102 | 8762 | SEK |
202211 | 89765 | USD |
It means I have reported different amounts for each month, and the amount can be in different currencies. Can I add a column to this table, that always shows the amount in EUR according to the year? I am using the exchange currency rate on a year basis like this:
Year | SEK | NOK | USD |
2021 | 10.1 | 10.3 | 1.18 |
2022 | 10.3 | 10.2 | 1.15 |
2023 | 10.8 | 10 | 1 |
Is this possible?
Thanx in advance!
Solved! Go to Solution.
Hey @sw123,
I recommend that you unpivot the currency table, this makes it easier to work with. You can do this dynamically in M.
YearCurrencyValue
2021 | SEK | 10,1 |
2021 | NOK | 10,3 |
2021 | USD | 1,18 |
2022 | SEK | 10,3 |
2022 | NOK | 10,2 |
2022 | USD | 1,15 |
2023 | SEK | 10,8 |
2023 | NOK | 10 |
2023 | USD | 1 |
Then you can apply the following DAX calculation as a calculated column:
EUR =
VAR _year =
{ LEFT ( 'Table'[Yearmonth], 4 ) }
VAR _currency =
{ 'Table'[Currency] }
VAR _rate =
CALCULATE (
SELECTEDVALUE ( 'Currency'[Value] ),
TREATAS ( _year, 'Currency'[Year] ),
TREATAS ( _currency, 'Currency'[Currency] )
)
VAR _result =
DIVIDE ( 'Table'[Amount], COALESCE ( _rate, 1 ) )
RETURN
_result
Everything for which no exchange rate can be found for the relevant year (in the case only for EUR), is always taken as 1 as a rate.
Hey @sw123,
I recommend that you unpivot the currency table, this makes it easier to work with. You can do this dynamically in M.
YearCurrencyValue
2021 | SEK | 10,1 |
2021 | NOK | 10,3 |
2021 | USD | 1,18 |
2022 | SEK | 10,3 |
2022 | NOK | 10,2 |
2022 | USD | 1,15 |
2023 | SEK | 10,8 |
2023 | NOK | 10 |
2023 | USD | 1 |
Then you can apply the following DAX calculation as a calculated column:
EUR =
VAR _year =
{ LEFT ( 'Table'[Yearmonth], 4 ) }
VAR _currency =
{ 'Table'[Currency] }
VAR _rate =
CALCULATE (
SELECTEDVALUE ( 'Currency'[Value] ),
TREATAS ( _year, 'Currency'[Year] ),
TREATAS ( _currency, 'Currency'[Currency] )
)
VAR _result =
DIVIDE ( 'Table'[Amount], COALESCE ( _rate, 1 ) )
RETURN
_result
Everything for which no exchange rate can be found for the relevant year (in the case only for EUR), is always taken as 1 as a rate.
Super! Thanx!
@sw123 , Unpivot the second table, then you can get the rate based on join
Unpivot Data(Power Query): https://youtu.be/2HjkBtxSM0g
New column in power query for year in table 1
Text.Start(Number.FromText([Month Year]),4)
You can merge both tables. You can select more than one column using the shift
: https://radacad.com/append-vs-merge-in-power-bi-and-power-query
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |