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.520272 USD 2019-09-01 00:00:00 0.633100 AUD 2019-08-01 00:00:00 0.936421 CAD 2019-08-01 00:00:00 0.871578 EUR 2019-08-01 00:00:00 0.594365 GBP 2019-08-01 00:00:00 0.539184 USD 2019-08-01 00:00:00 0.661486 AUD 2019-07-01 00:00:00 0.957282 CAD 2019-07-01 00:00:00 0.882298 EUR 2019-07-01 00:00:00 0.590196 GBP 2019-07-01 00:00:00 0.529500 USD 2019-07-01 00:00:00 0.672201
...
amount currencyIsoCode CreatedDate 9500.00 USD 2013-03-05 22:57:05 2000.00 USD 2013-03-09 00:40:55 10400.00 USD 2013-03-20 00:24:21 0.00 USD 2013-05-23 17:09:34 4500.00 NZD 2013-06-13 02:19:39 0.00 EUR 2013-06-19 08:35:28 0.00 USD 2016-03-09 05:47:17 5960.00 USD 2013-07-19 17:09:15 27000.00 USD 2013-08-16 18:05:33 6500.00 USD 2013-10-14 18:20:13 3990.00 USD 2013-10-31 22:42:16 6440.00 AUD 2014-01-07 19:22:58 420.00 AUD 2014-02-03 04:25:03 1200.00 GBP 2014-04-02 10:52:14 3500.00 USD 2014-04-16 03:15:24
...
I want to create a column that multiply amount with conversionRate depend on the related IsoCode and the CreatedDate in relation to StartDate.
For Example, the amount 9500 need to multiply with conversionRate of IsoCode USD with StartDate of '2013-03-01 00:00:00'
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])
1 Reply
- danextian
Super User
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])