Forum Discussion
Helper VExchange rate for specific date range based on posting date
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
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 vResultGlad 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
3 Replies
- DataInsights
Super User
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
IvanSThank 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
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