Exchange rate conversion with date range

[FaizanRao]

Hi

I need help to calculate sales in USD and Peso based on the exchange rate in Table 2.

Thanks

Faizan Rao

[Jihwan_Kim · Accepted solution]

LINK TO THE PBIX FILE 

[selimovd]

Hey @FaizanRao ,

as calculated column something like this should do it:

Sales in USD =
VAR vInvoiceDateCurrentRow = 'Table 1'[Invoice Date]
VAR vCurrencyCurrentRow = 'Table 1'[Sales Currency]
VAR vExchangeRate =
    CALCULATE(
        MAX( 'Table 2'[Exchange Rate] ),
        'Table 2'[Currency From] = vCurrencyCurrentRow
            && 'Table 2'[Currency To] = "USD"
            && 'Table 2'[Date From] <= vInvoiceDateCurrentRow
            && 'Table 2'[Date To] >= vInvoiceDateCurrentRow
    )
RETURN
    'Table 1'[Sales Value] * vExchangeRate

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[FaizanRao]

Hi Denis

Thanks for your prompt reply but still need your help to fix the following error.