Dynamic currency conversion

[carlomoretto]

Hello,

I'm struggling with an apparently simple task: dynamically converting fact table amounts to desired currency at the latest FX rate available, choosing the latest FX rate dynamically based on a slicer. (That is, also an amount from 01/10/1999 needs to be converted at the latest exchange rate as of the slicer date, not at the latest exchange rate available before 01/10/1999)

Link to the pbix: https://gofile.io/d/OHd4qO 

FactTable -> multiple transactions in multiple currencies (EUR is assumed as the pivotal currency)

FXRates -> Daily exchange rates USD/EUR and GBP/EUR

I've tried several solutions suggested in the community and I've managed to get the right values at transaction level and at the total level. Yet, the performance is terrible when I drill down to transaction level in a matrix.

What am I doing wrong?

Looking forward to your replies!

[amitchandak]

@carlomoretto ,

refer if these can help

https://blog.beyondimpactllc.com/blog/building-a-dynamic-currency-converter-with-power-bi

https://www.sqlbi.com/articles/currency-conversion-in-power-bi-reports/

[carlomoretto]

Hello @amitchandak !

Thank you for your input!

Unfortunately, both examples refer to cases that are much simpler to handle.

You can check my pbix to see the difference.

Do you know of any approach that would suit my case?

Thanks!!

[carlomoretto]

Hello amitchandak! Thanks for your reply!

I had checked both links:

1. Suggests a calculated column solution: however, I don't think a calculated column will work in my case, due to its nature
2. Has all amounts already in one currency and just converts them to another currency, which is a simpler case than mine

Any other solution that fits my case?

[v-deddai1-msft]

Hi @carlomoretto ,

Would you please refer to  https://radacad.com/currency-exchange-rate-conversion-in-power-bi-with-live-rates-part-2?

Best Regards,

Dedmon Dai

[carlomoretto]

Hello,

Thanks for your input!

I know that video actually. That is a static conversion though, ain't it?

I need the conversion to be dynamic based on the date selected by the user.