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)

    brian.jardine.0

      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)