I’m setting up a dashboard for a travel agent with 5k+ carried out in 3 different currencies. The set-up I have right now works, but I’m wondering if anyone can suggest improvements or more enhanced approaches to this case.
In the main table I’m extracting the ‘day’, ‘month’ and ‘year’ from the transaction date with formulas, then I get the user to link each ‘day’, ‘month’ and ‘year’ to the relevant Day, Month and Year tables where I can summarize the data accordingly. This is working fine but if you can think of a more elegant way to do it, I’d love to hear.
The main issue I’m facing is that:
I also need to convert transactions to a unique currency, so I prepared a different table with FX daily values - which I link to the main table (where all transactions are recorded) to pull the relevant exchange rate.
Is there a way to do so without having the user to link the again to a different date, in this casem in the FX base?
Any other schema/formula/functionality suggestion to streamline this set-up highly appreciated.