Average monthly currency exchange rate from daily exchange rate in Power BI

[abhayrnjn]

Hello All,

I want to create a calculate column where just want to create average monthly currency exchange rate from daily.

Please see screeshot below where we have daily average currency exchange rate and also here you will see single currency in FROMCURRENCY and the same in TOCURRENCY but we have multiple currencies in both the columns.

Below is the expected result to be shown.

Can anyone help me on this?

Thanks

[abhayrnjn]

Hi @MFelix,

I tried your solution but didn't work to me. So i had used below dax to get the expected result.

Monthly Average Rate =

var CurrentDate = ExchangeRateEntityStaging[STARTDATE]

var CurrentFrom = ExchangeRateEntityStaging[FROMCURRENCY]

var CurrentTo = ExchangeRateEntityStaging[TOCURRENCY]

Var MinDate = EOMONTH( CurrentDate, -1) + 1

Var MaxDate = EOMONTH( CurrentDate, 0 )

var Result =

AVERAGEX(

FILTER(

ExchangeRateEntityStaging, true

&& ExchangeRateEntityStaging[FROMCURRENCY] = CurrentFrom

&& ExchangeRateEntityStaging[TOCURRENCY] = CurrentTo

&& MinDate <= ExchangeRateEntityStaging[STARTDATE]

&& ExchangeRateEntityStaging[STARTDATE] <= MaxDate

),

ExchangeRateEntityStaging[RATE]

)

return

Result

Thanks for your help.

[MFelix · Accepted solution]

Hi @abhayrnjn ,

On the Query Editor Select the Group by and then do the grouping by:

• Group:
  • FromCrurrency
  • ToCurrency
• Aggregation
  • Average Rate
  • Max Date