Calculated column returning a single value based on filters from two other columns

[Anonymous]

Hi, 

Another hopefully Quick QU... 

I have a table of exchange rates and another table of sales that contain a Currency ID, for example, USD, GBP, EUR and a Transaction Date that contains the Year which corresponds with the year in the exchanges rate table. 

What I want to do is create a calculated column where it pulls in the exchange rate based on the Currency ID in the sales table and the Transaction Year. 

Then on the sales data table, there is the transaction column and a currency column which matches the currency ID table pictured 

What I have pictured is almost like an if statement 

If SalesTable[CurrencyID]=ExchangeRate[CurrencyID] && SalesTable[TransactionDate].Year=ExchangeRate[Year].Year return the ExchangeRate[Value] 

Seems so simple but is throwing me a massive spanner in the works. 

Ultimately this is to turn a multiple currency reports into a single $USD Value report using the exchange rate average from the same year as the transaction date year. 

Thanks again in advance, 

Josh

[v-yulgu-msft · Accepted solution]

Hi @Anonymous,

First, add a calculated column in exchange rate table to get the average of exchange rate per year per currency.

Average rate =
CALCULATE (
    AVERAGE ( ExchangeRate[Rate] ),
    ALLEXCEPT (
        ExchangeRate,
        ExchangeRate[CurrencyID],
        ExchangeRate[Year]
    )
)

Then, add a calculated column in sales table to return the proper rate value which matches the Currency ID and Transaction Year.

Rate =
LOOKUPVALUE (
    ExchangeRate[Average rate],
    ExchangeRate[CurrencyID], SalesTable[CurrencyID],
    ExchangeRate[Year], SalesTable[TransactionDate].YEAR
)

Best regards,

Yuliana Gu

[Anonymous]

maybe by creating a new dax measure and giving each type of currency ID a certain value, you can then create your argument for the data you want to present.