Forum Discussion
Resolver IIAverageIfs in DAX / LOOKUPVALUE
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!
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])
3 Replies
a68tbirdThank you both, Greg_Deckler and Ashish_Mathur . I used your solution Greg_Deckler , and included currency in the filter too. Works perfectly!
- Greg_Deckler
Community Champion
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]) - Ashish_Mathur
Super User
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.
