kelvin-lkh
New Member
Dear all experts,
First of all, I would like to share with you that I have posted this qns on other forum and I do understand that Chandoo forum does not allow the multiple posting qns. However, I couldn't get the solution after a while it has been posted on other forum. Hence, I would like to seek help if anyone here know the answer to my qns. Please pardon me if my case is still considered violating the rule of this forum.
The challenge I encounter as below
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?
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.
First of all, I would like to share with you that I have posted this qns on other forum and I do understand that Chandoo forum does not allow the multiple posting qns. However, I couldn't get the solution after a while it has been posted on other forum. Hence, I would like to seek help if anyone here know the answer to my qns. Please pardon me if my case is still considered violating the rule of this forum.
The challenge I encounter as below
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?
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.