Manage EUR cst & FX columns in Transaction List

Question

Hello,

In my Transaction List, I have a column called EUR CST that I calculate from a variable FX column. However, this EUR CST is associated with rates for the year 2022.
We have just received the first figures for 2023, but we are now using a different rate (the rates are in a transaction list, with two columns: rate 22 and rate 23).
What is the best way/best practice to switch to rate 23 for the 2023 figures?

However, I would like to avoid doing this every year (in 2024, it would be nice if it automatically switched to the 2024 rate). Is there a way to do this? Because all my metrics depend on this column.

Thank you,

Alexandre

icon

Best answer by Keiran 15 February 2023, 19:24

View original

Keiran · Accepted Answer

Hi Alexandre,Hopefully I understood your query, I’ve taken a look at this for you - from my understandingyou are creating a new column in the transaction list for every year, this limits the opportunities to limit the manual intervention required.I came up with a simple transaction list, containing the period, the value and the FX RateI’ve then used a simple formula to pull the relevant FX rate by year for the calculationsIF('Transaction List'.'Order Year' = Year."FY 23", 'Transaction List'.'Rate 23',IF('Transaction List'.'Order Year' = Year."FY 24", 'Transaction List'.'Rate 24',BLANK))I believe it would be better to create a Dimension for your CurrenciesAnd then create a simple metric for FX Rates (Structured Month x Currency)You can then use the Formula PREVIOUS(Month), allowing manual inputs on the metric, and import the new value in each month.Then in the Transaction List pull your FX rate for the relevant currency'FX Rates' [BY : 'Transaction List - Metric FX'.'Order Month', 'Transaction List - Metric FX'.'Order Currency']And then complete your calculation.I hope this makes sense, and helps solve your problem! If it does not if you could anonymize two example lines of data and send a screenshot I am happy to try to help further! Thanks :)