Currency translation by date

DAX DAX Calculations
1

Hi friends,
I have two tables:

  1. transaction date, currency code, transaction amount
  2. effective date, currency code, exchange rate
    1transaction date and 2effective date have a relationship

This seems relatively simple but I’m drawing a blank right now. Do you have a recommendation for the most effective way to use DAX to reference date and currency code for each transaction to normalize all sales to USD to create a new column in table 1 “amountUSD”?

Thanks!

2

@Brandon,

Have you checked out @Nick_M’s recent video on this topic?

  • Brian
3

Hi @Brandon
Sure, just create a calculated column and get the exchange rate using RELATED. The RELATED function transforms the row context in a filter context so you can import the rate taking into account the date and the currency type.
=DIVIDE(Transactions[Amount];RELATED(FX[DailyRate]));0)

Best!
DJ

4

Hi @Brandon, did the response provided by the contributors help you solve your query? If not, how far did you get, and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

5

Thanks for the help! The video was excellent and the dax Diego shared was very helpful. I ended up writing some sql to satisfy the requirement BEFORE the data gets to the model rather than modelling after.
Thanks again,
Brandon

1 Like