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.