Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello All -
I'd like to add a calculated column to my Sales table (AllSales) that will return the relevant exchange rate. The ExchangeRate table has daily rates, in four currencies. What I would like to do is lookup the currency and date of transaction and return the monthly average. I have been able to do this in Excel as such:
AVERAGEIFS(ExchangeRates[Rate],ExchangeRates[Currency],cell_with_Currency,ExchangeRates[Year],YEAR(salesDate),ExchangeRates[ClosingMonth],MONTH(salesDate))
So if the sale was on November 15, 2021, in USD, the formula would return the average USD closing rate for November.
Trying to replicate this in DAX has me stumped. I've tried different combinations of AVERAGEX with FILTER and LOOKUPVALUE, but usually get a "too many values" returned error.
Any suggestions?
Thanks!
Solved! Go to Solution.
@a68tbird Try something like:
Column =
VAR __Date = [salesDate]
VAR __Year = YEAR(__Date)
VAR __Month = MONTH(__Date)
RETURN
AVERAGEX(FILTER(ALL('ExchangeRates'),'ExchangeRates'[Year] = __Year && 'ExchangeRates'[ClosingMonth] = __Month),'ExchangeRates'[Rate])
Thank you both, @Greg_Deckler and @Ashish_Mathur . I used your solution @Greg_Deckler , and included currency in the filter too. Works perfectly!
Hi,
Write these calculated column formulas in the Data Table
Year = year(data[sales date])
Month number = month(data[sales date])
Exchange rate = calculate(average(rate[exchange rate]),filter(rate,rate[year]=earlier(data[year])&&rate[month number]=earlier(data[month number])&&rate[currency]=earlier(data[currency])))
Hope this helps.
@a68tbird Try something like:
Column =
VAR __Date = [salesDate]
VAR __Year = YEAR(__Date)
VAR __Month = MONTH(__Date)
RETURN
AVERAGEX(FILTER(ALL('ExchangeRates'),'ExchangeRates'[Year] = __Year && 'ExchangeRates'[ClosingMonth] = __Month),'ExchangeRates'[Rate])
User | Count |
---|---|
93 | |
83 | |
77 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |