I have researched a lot and struggling with this one, I wonder if you can help.
Exchange Rate Table “ExRates” has Code, Date, ToGBPRate
Each currency (USD, and 70+ others) has rates, usually we are downloading a daily set of rates. But, for some currencies we stopped getting rates, or maybe some issue arose, and the latest rate maybe is a month ago.
I want to convert the currency bank account amounts back to GBP to assess “what are they worth in terms I understand” but I ALWAYS want this at the latest rate. That is, for example, I want today’s USD balance calculated at today’s rate. Not the date of the bank posting or some other “transaction” date. Always at latest rate available for that currency in the ExRates table.
All the exchange rate examples I found using LOOKUPVALUE seem to use rate at Order Date, Sale Date, or something.
I would like to do something like:
ExRates[Date],"MAX of the ExRates[Date] for that particular ExRates[Code]"
I am not averse to making a table each day of the latest currency exchange rates separate from the ExRates history table, if you think this would be easier (ideas on how I code that welcome )
This pattern would apply to Stock Exchange Price histories too, where we don’t always get an instrument price daily for every instrument (e.g. some are priced monthly, some daily etc).