cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Currency exchange on yearly basis

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?

1 ACCEPTED SOLUTION
Solution Sage

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.

3 REPLIES 3
Solution Sage

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.

Helper III

Super! Thanx!

Super User

@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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors