AverageIfs in DAX / LOOKUPVALUE

[a68tbird]

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]

Thank you both, @Greg_Deckler  and @Ashish_Mathur .  I used your solution @Greg_Deckler , and included currency in the filter too.  Works perfectly!

[Ashish_Mathur]

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.

[Greg_Deckler · Accepted 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])