I have a question regarding an extension of the P&L Structure; adding a Slicer that would change the currency. The result should be a slicer, where one can select local currency, Dollar, GBP, etc. so that the values in the specified P&L are calculated accordingly.
The build of the model is as follows:
One transaction table with all the financial data (in local currency)
One table with the exchange rates, where column one specifies the curreny name and the second column specifies the exchange rate
The Structure and DAX-Formulas are pretty much similar to the exmample:
My question is how to integrate the necessary DAX-modification in the “Selected Year Actuals” measure, if at all? Or is there a better way to impelent the Slicer?
@RolandAnkele Hi, You could add another variable to check the currency and then you could output the value based on the selected currency.
It also depends where you want to grab the exchange rate, how it’s stored in your data and how you want to revalue transaction balances at year-end.
It’s an interesting problem.
But my experience with financial tempaltes is that having a template to switch between currencies may make the report really slow…The way these templates works does put a fair load on the analysis engine… maybe too many measures to input using this method.
A better option may be to split the template one for local and one for foreign but you can try having everything in one template to start with.
Hi @RolandAnkele we’ve noticed that no response has been received from you since Nov 21, 2019. We just want to check if you still need further help on this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. You may reopen a new thread when the need arise.
I have figured 2 ways to implement the FX-Calculations within the formula, yet I decided to restructure my data [in Power Query] and add a currency dimension.
The negative implication for making FX-calculations in DAX-Measures is that I would have to embed almost every measure within the BI-Report with the Formula (since I need a local currency to FX-currency swith for all ilustrations). Also, as you have already pointed out, the performance seems to suffer with too many Measures in this specific P&L-Template. Increasing the Data in the Model (Query) by 3MB (Total 10MB File now) seems to work better.
For anyone interested, here are the formulas. Both do the same thing; I am not sure which one is the more efficient code:
@RolandAnkele What I have found is it more efficient to have fact tables in the currency you require. Most accounting systems have a currency dimension table and the fact tables are tagged with the relevant currency of the transactions.
If you converting base currency financials into other currencies at a rate then templates for each may be the easiest option. Good luck and thanks for your supporting the forum.
Hi @RolandAnkele, a response to this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!
