Forum Discussion
Help with Currency Conversion, Need to have Fixed Rate and Transaction Rate
I have transactional data for two countries that needs to be reported in a single reporting currency (GBP). I’ve set up a toggle that lets me choose between:
- Fixed rate – all historical transactions are converted using the latest year selected in the slicer.
- Transaction rate – transactions are converted based on their actual transaction date.
I’ve successfully written the Reporting Amount measure, but I’m struggling with the Reporting Amount Prior Year.
The issue: when I select Constant for Prior Year, both the transactions and the rates are being pulled from 2025. What I actually need is for only the transactions to shift to 2025, while the rates remain anchored to the slicer selection.
I’ve tried a solution suggested by CoPilot, but it requires duplicating a lot of measures, which isn’t scalable. Ideally, I’d like to have a single GBP Amount PY measure that I can branch other calculations from.
Thanks All
I tried a different approach and it works well for handling transactions across different dates. The idea is to use time intelligence to shift the transactions while keeping the rate difference (fixed vs transaction) as a constant. This way, the transactions convert according to the desired date logic, and the rate difference variable simply determines whether the calculation uses the fixed rate or the transaction rate
9 Replies
I tried a different approach and it works well for handling transactions across different dates. The idea is to use time intelligence to shift the transactions while keeping the rate difference (fixed vs transaction) as a constant. This way, the transactions convert according to the desired date logic, and the rate difference variable simply determines whether the calculation uses the fixed rate or the transaction rate
- DanieleUgoCopp
Super User
Hello,
It sounds like your filter context is shifting both fact and rate tables together, instead of separating themone approach is to override the date filter only for the rates part of the measure, something like using CALCULATE with REMOVEFILTERS or ALL on the rate date, while keeping SAMEPERIODLASTYEAR (or your PY logic) on the transaction side, so basically you “freeze” the rate context to the slicer year and only let the transaction dates move.
Another option is to store the selected year in a variable and explicitly filter the rate table to that year inside the measure, so PY logic doesn’t touch itif your model allows it, keeping rates in a disconnected table and applying them via TREATAS can also help avoid this kind of context bleed
Best regards,
DanieleThanks, you are correct that the filter context is shifting both the transactions and rate. I have tried many different ways but can't work out how to freeze the rate context.
The date table and rate table is already disconnected.
- FBergamaschi
Hi HinW12
as a first support, here you see what is exactly happening, you can inspect the rows retrieved and see if they are what you expect or not.
The file is attached with some comments on the measure GBP Amount PY that I changed to show you the rows that are subject to your DAX code.
I hope this helps to understand if the numbers you are seeing are or not what you were looking for, then we can fix
If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Thanks FBergamaschi for breaking down this information.
The solution provided by AI was split into two variables and force the rate. Just confirming there isn't a way to just shift context of transactions but keep the rate.
- FBergamaschi
HI HinW12
so are the nrs shown the correct ones? If not, which is wrong, so we fix it?
Best
If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
v-pnaroju-msftCommunity Support
Thankyou, DanieleUgoCopp and FBergamaschi for your responses.
Hi HinW12,
We appreciate your question on the Microsoft Fabric Community Forum.
Based on my understanding of the issue, please find attached a screenshot and a sample PBIX file that may assist in resolving the matter.
We hope the information provided helps to resolve the issue. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.Thank you.
- v-pnaroju-msft
Hi HinW12,
We are pleased to note that your issue has been resolved. Thank you for sharing your insights and approach in resolving the issue, which will be beneficial to other members of the community. Should you have any further queries, please feel free to contact the Microsoft Fabric community.
Thank you.
