refer a column from another table through date, but the reference table has date range

[Anonymous]

I have two tables.

CurrencyTable:

IsoCode	StartDate	NextStartDate	ConversionRate
AUD	5/1/2019	7/1/2019	0.94485
CAD	5/1/2019	7/1/2019	0.892392
AUD	7/1/2019	12/1/2019	0.9568
CAD	7/1/2019	12/1/2019	0.8872

OppsTable:

Id	Iso	CloseDate	Amount
1	AUD	5/25/2019	71.00
2	CAD	6/12/2019	125.20
3	CAD	7/9/2019	1.10
4	CAD	11/25/2019	50.00
5	AUD	6/29/2019	100.00

I want to create a NZDollar column and calculate the NZDollar amount from these two tables.

For example NZDollar for Id:2 in OppsTable would be (125.20/0.892392)

[amitchandak · Accepted solution]

Populate rate in opps table as a New Column

Rate = maxx(filter(currencyTable, currencyTable[startdate]<=opps[CloseDate] && currencyTable[nextDate] >opps[CloseDate] && currencyTable[isocode]<=opps[isocode]),currencyTable[conversionRate])
Then
NZ dollor = currencyTable[Amount]/currencyTable[Rate]

Another way is to use lookupvalues.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

@amitchandak Worked like a charm. Thanks for the solution. 

PS 

currencyTable[isocode]=opps[isocode])