Value*value from another table depending on month

[Anonymous]

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'

[danextian · Accepted solution]

hI @Anonymous,

Try this:

1. Convert both startdate and CreatedDate columns to type date.
2. 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]
3. Create a relationship between the two new columns.
4. 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])