topic Re: Monthly Average of a column (Exchange Rate) in DAX Commands and Tips

Monthly Average of a column (Exchange Rate)

Anonymous — Thu, 22 Jul 2021 13:40:53 GMT

Hi,

I have an exchange rate master in my model, with following columns:

Conversion Date
Currency From
Currency To
Conversion Rate

I want to create a calculated column using Dax that gives Monthly Average Conversion Rate (aggregated on Currency From and Currency To). Also please note that Conversion rates might not be available for every date in a month, hence average needs to be calculated accordingly i.e. based on available rates only.

Please help!

Thanks in advance,

Shailee

Re: Monthly Average of a column (Exchange Rate)

Anonymous — Thu, 22 Jul 2021 14:03:06 GMT

I found the solution:

Monthly Average Conversion Rate =

Var _Month = MONTH('Exchange Rate Master'[Conversion Date])

Var _Year = YEAR('Exchange Rate Master'[Conversion Date])

Var _MinDate = DATE(_Year,_Month,1)

Var _MaxDate = ENDOFMONTH('Exchange Rate Master'[Conversion Date])

Return CALCULATE(AVERAGE('Exchange Rate Master'[Conversion Rate]),FILTER(all('Exchange Rate Master'),'Exchange Rate Master'[Conversion Date]>=_MinDate && 'Exchange Rate Master'[Conversion Date]<=_MaxDate && 'Exchange Rate Master'[Currency From]=EARLIER('Exchange Rate Master'[Currency From]) && 'Exchange Rate Master'[Currency To]=EARLIER('Exchange Rate Master'[Currency To])))

Let me know if there is any better solution!

Re: Monthly Average of a column (Exchange Rate)

daxer-almighty — Sat, 24 Jul 2021 11:24:57 GMT

// There's always a better solution... // You should know that you should avoid // CALCULATE in calculated columns for // performance reasons. [Monthly Average Conversion Rate] = var CurrentDate = 'Exchange Rate Master'[Conversion Date] var CurrentFrom = 'Exchange Rate Master'[Currency From] var CurrentTo = 'Exchange Rate Master'[Currency To] Var MinDate = EOMONTH( CurrentDate, -1) + 1 Var MaxDate = EOMONTH( CurrentDate ) var Result = AVERAGEX( FILTER( 'Exchange Rate Master', true && 'Exchange Rate Master'[Currency From] = CurrentFrom && 'Exchange Rate Master'[Currency To] = CurrentTo && MinDate <= 'Exchange Rate Master'[Conversion Date] && 'Exchange Rate Master'[Conversion Date] <= MaxDate ), 'Exchange Rate Master'[Conversion Rate] ) return Result

Re: Monthly Average of a column (Exchange Rate)

Anonymous — Mon, 26 Jul 2021 10:17:09 GMT

Thanks, will keep that in mind!