Reporting with exchange rate adjustments

[RossBateman96]

Hello

I have created a report with GBP and EUR balances. I need to convert all those balances either by a closing rate or an average rate. For example, for June's reporting I need a measure to calculate the average 2024 rate (1.155) and another one to calculate the closing rate (1.18). This is from a table filled with dates and exchanged rates. This can then be applied to the amounts based on another field (Income statement or Balance Sheet).

Please can suggestions be shared for the formula required?

Thanks in advance

[v-xingshen-msft · Accepted solution]

Hi @RossBateman96 ,
I tried to write some example data based on your question, hopefully it will help you, my first step was to use June as an example and calculate the average rate for June to emulate your average rate for the whole year 2024.

AverageRate2024 = CALCULATE(
    AVERAGE('Table'[Rate]),'Table'[Date]>=DATE(2024,6,1)&&'Table'[Date]<=DATE(2024,6,30)
)

In the second step, since you are not sure if your settlement date is the end of each month, in the second function I use a dynamic date to ensure that you can select that date as the exchange rate for your own settlement date.

ClosingRateJune2024 = 
VAR A=SELECTEDVALUE('Table'[Date])
RETURN
 CALCULATE(
     MAX('Table'[Rate]),
     'Table'[Date]=A
)

The third step is to make a judgement call to see which exchange rate to use for different types.

I hope my thoughts have addressed your concerns, and I would be honoured if I could help you out!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.