#Currency converter by year how to#

#Currency converter by year how to#

It is just one unique value - the last in the time period shown.įrustratingly I still can't work out how to link the values above to the convert measure you created. Behind the scenes, the date that value came in must be used to lookup in the currency conversion table to get the rate to the selected currency on that day, if it is a different selection.įortunately we are not dealing with multiple dates that need to be summed - each value summed up needing to be converted on their date. So for example Name1 here had the final value in 2020 as 23.00 in its native currency.

Table A here - just to be clear - the row totals by Asset Name are simple the last value in the column time period. Before the currency conversion issue, value dates were pulling into Table A perfectly: With frustration, I am still trying to work out how to reference the convert measure you supply into the other existing measures. (all these rates in the currency converstion table are to EUR) That certainly handles all cases of conversion I think! So in my original example, in the case where an asset currency is in DKK and my selected currency is to AUD the logic is: ROW ( 'CURRENCY',, 'TIME_PERIOD', mcd again. ROW ( 'CURRENCY', mc, 'TIME_PERIOD', mcd ), Here is a refactored version that first checks if the selected currency is the same as the source currency, then checks if the selected currency is EUR, and then handles all other cases. As you state you need to find the maximum date in the conversion table that is smaller or equal to your transaction date in Table B. It's a bit meaty this one but any help at all I'd be grateful for.

There is an API connection to ECB to get the currency conversion rates for relevant currencies I need. I've attached a sample file with this dummy data in for reference. * = 7.4392 ( this one is simple as the rate matches that from the ECB extraction) So in all cased the amount will need to be converted to Euros and then out to the native currency again for situations where the asset's native currency is DKK, and the user has selected something like AUD for the value to be converted to. The added problem is that ECB only give a rate to Euros. So there are several different native currencies listed/referenced, so the native currency against the asset needs to be looked up, this is then used in the currency conversion rates table against the date against for the converstion and used to adjust the value. The maybe tricky part is that for each row in Table A (each Asset listed in the row) which contains the Asset Value, displays the native currency value as standard as per in the model view the currency column in Table A. When this currency from the drop down is selected I need all the values in my table ('table A') changed using this exchange rate. I need a bit of help referencing a currency table and using the exchange rate provided in that table, against the relevant date, to convert the value to the currency the user has selected in a drop down on the report.