Forum Discussion
Exchange rate table
My core sales data has a mix of GBP and EUR. I want to be able to translate the EUR into GBP value based on the rate on the day the sale occured.
I can bring in an exchange rate table that contains historic rates for EUR but I can't get the formaula to work and think it's because of the GBP.
I can't seem to get a formula to work that will say if GBP return the GBP value but if it's EUR then match the date to the exchange table and calculate.
Any ideas
TIA
4 Replies
Anonymous , We usually have from curr , to curr , date and rate and use that to create a new column
New column in Fact = maxx(filter(fact, Fact[Date]= rate[Date] && Fact[from Curr]= rate[Curr] && [To Curr] ="GBP") ,Fact[Rate])
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
also refer
https://www.sqlbi.com/articles/currency-conversion-in-power-bi-reports/
https://radacad.com/currency-exchange-rate-conversion-in-power-bi-with-live-rates-part-2
Hi Anonymous ,
Please show sample data of the two tables so we can help you to come up with the formula.
I tried to take out the sensitive data and it broke it completely by telling me it couldn't create a relationship between the currency data and my core sales data.
I can get the formula to work providing the currency isn't GBP so if I have EUR, JPY, SEK it works providing i have the rates in the table, but i just need some kind of statement that will return the value in it's raw from if the Currency is in GBP. In excel i can do this very easily but i can't get it to work in DAX
