Forum Discussion
Value*value from another table depending on month
I have two tables that looks like this. IsoCode startdate conversionRate
AUD 2019-09-01 00:00:00 0.936421
CAD 2019-09-01 00:00:00 0.842502
EUR 2019-09-01 00:00:00 0.575291
GBP 2019-09-01 00:00:00 0....
hI Anonymous,
Try this:
- Convert both startdate and CreatedDate columns to type date.
- Created a calculated column in both tables to concatenate both currency and date. Example:
- Int table 1 - Concat = Table1[IsoCode] & Table1[startdate]
- Int table 2 - Concat = Table2[currencyIsoCode] & Table2[CreatedDate]
- Create a relationship between the two new columns.
- Assuming that Table1 will hold the unique values for concatenated IsoCode and startdate column (this will be the one side of the one to many or many to one relationship), create a new calculated column in Table 2 that goes like this:
- Converted Rate = Table2[amount] * related(Table1[conversionRate])
danextian
Super User
Super UserhI Anonymous,
Try this:
- Convert both startdate and CreatedDate columns to type date.
- Created a calculated column in both tables to concatenate both currency and date. Example:
- Int table 1 - Concat = Table1[IsoCode] & Table1[startdate]
- Int table 2 - Concat = Table2[currencyIsoCode] & Table2[CreatedDate]
- Create a relationship between the two new columns.
- Assuming that Table1 will hold the unique values for concatenated IsoCode and startdate column (this will be the one side of the one to many or many to one relationship), create a new calculated column in Table 2 that goes like this:
- Converted Rate = Table2[amount] * related(Table1[conversionRate])