Currency exchange on yearly basis

[sw123]

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!

[Barthel · Accepted 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.

[sw123]

Super! Thanx!

[amitchandak]

@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