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.
Hopefully I understood your query, I’ve taken a look at this for you - from my understanding you 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 Rate
I’ve then used a simple formula to pull the relevant FX rate by year for the calculations
I believe it would be better to create a Dimension for your Currencies
And 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
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 :)