How to auto convert to amount with the Exchange rate based on the Year selected in the Slicer

[kelvin-lkh]

Hi PBI Experts,

I create two tables below. The Col Amount in the Data table is in original currency and split by year. I am not able to create a relationship between two tables due to there is no singapore with unique value.

The thing I want to achieve is I would be able to make the amount in the matrix table covert based on the respective rate of the year I have selected in the slicer. For instance, when I select year 2021, the amount of every year (2020, 2021, 2022) will be auto converted based on the 2021 rate of the respective currency.

Can you please advise how I can do that? By using a Dax formula or I need to modify the table and create a column in the Data table to convert based on the year I select?

I want to attached the files but I cannot locate where I can add the files in this post.

Table - Data

Table - Currency

Model : unable to create relationship between 2 tables due to there is no any column with unique data

Matrix Table in Original Currency. Ideally the data in Matrix table should auto convert to the USD based on the rate of the year.

[kelvin-lkh]

@amitchandak  Can I know if there is any solution for the calculation issue I have encountered in your knowledge?

Thanks.

[kelvin-lkh]

@amitchandak Thanks for the suggestion. However, the solutions you have suggested should be meant for convert the data of the year with corresponding yearly exchange rate, meaning 2020 data with 2020 exchange rate, 2021 data with 2021 exchange rate.

What I need is to convert the data of every year (data of 2020, 2021, 2022) with the year of change rate I have selected from the slicer. For instance, when I select year 2021 under the slider, all the data (data of 2020, 2021, 2022) will be converted with 2021 exchange rate of respective currency. if I select year 2022, all the data (data of 2020, 2021, 2022) will be converted with 2022 exchange rate of respective currency. 

Hope my ellaboration above helps to understand what I try to achieve.

[amitchandak]

@kelvin-lkh , Create a combine key inboth Tables

key = [Year] & "-" & [Curr]

Join both table on this 

and then you can get rate in data table like in a new column

Rate = related(currency[Rate])

or in measure

Amount = Sumx(Data, Data[Value]*related(currency[Rate]) )

refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8