Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Currency Conversion using historical Conversion Table

Good evening Community!   I have a problem that I am finding difficult to solve.  I have read and read and read, but I can't not find a solution that works.  I am trying to do Currency Conversion f...
Need Help
Show and Tell
Tips and Tricks
  • parry2k's avatar
    parry2k
    5 years ago

    Anonymous have relationship between these two tables on Transaction Date , it will be many to many, on relationship page set relation that "Sales filter Currency table"

     

    and now add the following measure

     

    SUMX ( SalesTable, CALCULATE ( SUM ( SalesTable[Amount] ) ) * CALCULATE ( MAX ( ExchangeTable[ExchangeRate] )

     

    add slicer on Currency code and have it single select so that a currency is selected.

     

    Use this new measure to get sales in the selected currency.

     

    Check my latest blog post Compare Budgeted Scenarios vs. Actuals to get a summary of my favourite Power BI feature releases in 2020

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

parry2k's avatar
parry2k
Super User
5 years ago

Anonymous have relationship between these two tables on Transaction Date , it will be many to many, on relationship page set relation that "Sales filter Currency table"

 

and now add the following measure

 

SUMX ( SalesTable, CALCULATE ( SUM ( SalesTable[Amount] ) ) * CALCULATE ( MAX ( ExchangeTable[ExchangeRate] )

 

add slicer on Currency code and have it single select so that a currency is selected.

 

Use this new measure to get sales in the selected currency.

 

Check my latest blog post Compare Budgeted Scenarios vs. Actuals to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    That did the trick!  I think i was too wrapped up in the dimDate table.

     

    Thank you so much!