3 Replies Latest reply on Jan 19, 2018 3:58 AM by Zhouyi Zhang

# Calculate sum of amounts using most recent currency conversion rate (Balance Sheet)

If anyone has any suggestions to tackle the following it would be much appreciated.

The scenario is that we have a dataset with buildings, a transaction amount, transaction category, date of transaction and the currency conversion rate at the time of that transaction date.  In the completed report we want to sum up all the amounts over a period of time broken down by category for a selected currency e.g.

Assets : 123,322,000

Liabilities : 543,999

It would be easy enough to carry out the currency conversion at row level and sum that up as we have all the required fields at that level i.e. amount and conversion rate but what is required is to sum up the amounts at building level (each unique building will have the same currency) but instead of using the currency conversion rate at that transaction date we need to use the most recent currency conversion rate in the dataset which is less than or equal to the reporting date.

e.g. we have the following two records for a building and we want to see the value in EUR currency with reporting date of January 2018

date = 2017-01-01

name = Building 1

amount = 100

currency = USD

EUR conversion rate = 1.5

date = 2017-06-30

name = Building 1

amount = 100

currency = USD

EUR conversion rate = 1.6

We want to add both values together and then multiply by the latest conversion rate which is less than or equal to reporting date of Jan 2018 i.e.

(100 + 100) * 1.6

Is there any way to do this?  We are basically mixing aggregation levels (have tried Level of Detail calcs with not much success)

Please see attached example workbook (Balance Sheet worksheet)

• ###### 1. Re: Calculate sum of amounts using most recent currency conversion rate (Balance Sheet)

Hi, Brian

Is this expected result for EUR as example? if yes, please find the attached workbook with my updates

Hope this could help

ZZ

1 of 1 people found this helpful
• ###### 2. Re: Calculate sum of amounts using most recent currency conversion rate (Balance Sheet)

Hi Zhouyi - yes!  That is exactly what I needed!

Many thanks for that.

Brian.