Exchange rate for specific date range based on posting date

[IvanS]

Dear all,

could you please help me with writing following DAX formula as calculated column "Exchange rate" in my FACT_Invoices table.

I have table DIM_ExchangeRates that contains rates that are updated with irregular frequency - only in case of significant rate change. As the table consists of duplicates, there is no relationship to to the FACT_Invoices table.

Currency	Exchange Rate	Valid From
PLN	4.3	1.1.2020
USD	1.2	1.3.2020
PLN	4.5	1.6.2021
USD	1.1	1.1.2022
PLN	4.6	1.1.2022

Sample of FACT_Invoices tables is following. I am trying to get the exchange rate valid for the specific currency and valid within the given date range from DIM_ExchangeRates table. If the currency is EUR, then set the exchange rate to 1, as the next step will be to create simple DIVIDE function Amount in EUR = Amount/Exchange rate.

Invoice No.	Amount	Posting Date	Currency	Exchange rate	Amount in EUR
1	1000	15.3.2021	EUR	1
2	50000	31.5.2021	PLN	4.3
3	500	24.5.2020	EUR	1
4	3000	1.11.2021	USD	1.2
5	90000	30.12.2021	PLN	4.5

Thank you for any help!

Ivan

[DataInsights · Accepted solution]

@IvanS,

Try this calculated column in FACT_Invoices:

Exchange rate = 
VAR vPostingDate = FACT_Invoices[Posting Date]
VAR vCurrency = FACT_Invoices[Currency]
VAR vMaxValidFrom =
    CALCULATE (
        MAX ( DIM_ExchangeRates[Valid From] ),
        DIM_ExchangeRates[Currency] = vCurrency,
        DIM_ExchangeRates[Valid From] < vPostingDate
    )
VAR vRate =
    CALCULATE (
        MAX ( DIM_ExchangeRates[Exchange Rate] ),
        DIM_ExchangeRates[Currency] = vCurrency,
        DIM_ExchangeRates[Valid From] = vMaxValidFrom
    )
VAR vResult =
    IF ( vCurrency = "EUR", 1, vRate )
RETURN
    vResult

[DataInsights · Accepted solution]

@IvanS,

Try this calculated column in FACT_Invoices:

Exchange rate = 
VAR vPostingDate = FACT_Invoices[Posting Date]
VAR vCurrency = FACT_Invoices[Currency]
VAR vMaxValidFrom =
    CALCULATE (
        MAX ( DIM_ExchangeRates[Valid From] ),
        DIM_ExchangeRates[Currency] = vCurrency,
        DIM_ExchangeRates[Valid From] < vPostingDate
    )
VAR vRate =
    CALCULATE (
        MAX ( DIM_ExchangeRates[Exchange Rate] ),
        DIM_ExchangeRates[Currency] = vCurrency,
        DIM_ExchangeRates[Valid From] = vMaxValidFrom
    )
VAR vResult =
    IF ( vCurrency = "EUR", 1, vRate )
RETURN
    vResult

[IvanS]

Thank you very much! Very helpful solution! Just one small adjustment in the code - can you please change that in your post for other users?

DIM_ExchangeRates[Valid From] <= vPostingDate

[DataInsights · Accepted solution]

@IvanS,

Glad to hear that worked. Thanks for letting me know--I've updated the DAX:

Exchange rate = 
VAR vPostingDate = FACT_Invoices[Posting Date]
VAR vCurrency = FACT_Invoices[Currency]
VAR vMaxValidFrom =
    CALCULATE (
        MAX ( DIM_ExchangeRates[Valid From] ),
        DIM_ExchangeRates[Currency] = vCurrency,
        DIM_ExchangeRates[Valid From] <= vPostingDate
    )
VAR vRate =
    CALCULATE (
        MAX ( DIM_ExchangeRates[Exchange Rate] ),
        DIM_ExchangeRates[Currency] = vCurrency,
        DIM_ExchangeRates[Valid From] = vMaxValidFrom
    )
VAR vResult =
    IF ( vCurrency = "EUR", 1, vRate )
RETURN
    vResult