I'm trying to connect 2 independent datasources and make a calculated field from one datasource using the most recent value.
First datasource shows the cash balance on different dates and for different currencies.
Second datasource shows the exchange rates on different dates and for different currencies.
I linked the 2 sources with the currency, but I don't want to link the date because we don't have any exchange rate for the future date.
I would like to make a calculated field that use the most recent exchange rate for every date and then shows the balance cash in CAD.
In the example attached, in every currency I would like to take the most recent exchange rate (so the one for the 2016-05-19).
Is it possible to do that?
The second datasource gives the exchange rate from today only at 11:30. So before this time, we have to take the exchange rate from yesterday. However, when it's a Monday, the exchange rate from yesterday is not available so we have to take the one on Friday.
If you have any question about my issue, please don't hesitate to contact me.
Thanks for your help,
If I try Max([Date]), it takes the most recent date, but it doesn't take the most recent rate... It takes the average of all the rates.
How do I make my calculation field for the balance cash in CAD?