Monthly Average of a column (Exchange Rate)

[Anonymous]

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

[Anonymous · Accepted solution]

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!

[daxer-almighty · Accepted solution]

// 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

[Anonymous]

Thanks, will keep that in mind!

[Anonymous · Accepted solution]

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!