Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
Proud to be a Super User!
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
Proud to be a 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
Proud to be a Super User!
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
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
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |