Cumulative sums to month end dates, then apply a different FX rate for each month's cumulative sum

[jhilton]

Out of our ERP we get a list of transactions from the start of time (2021) in each company's local currency, and to create a balance sheet by local currency we sum all the historic transactions up to the balance sheet closing date (eg 31/Dec/2024 will be a sum from 1/Jan/2021 to 31/Dec/2024 inclusive etc).

We then convert cumulative sum from the local currency to USD using the month end closing rate for the month end we want to display. 

For example to convert the UK entity in GBP to USD:

31/Dec-24 USD column = (all transactions up to 31/Dec/2024 in local currency) * 1.25359262

31/Jan-25 USD column = (all transactions up to 31/Jan/2025 in local currency) * 1.24161803

28/Feb-25 USD column = (all transactions up to 28/Jan/2025 in local currency) * 1.25897052

And we would have different fx rates for EURO, USD, PLN entities etc and they all get converted to USD using the correct month end rates for the balance sheet date.

Currently we create a USD total by manually by creating new columns/measures for each month end date which sums up all historic transactions, and then devides by the right FX rate etc. Then manually add these new columns to the tables eachmonth - so not ideal or best practice in any way, shape or form!

What is the best practice to do this?

We have the following existing fields:

TBs[Group FS Level 3] - the chart of accounts that things are grouped by in the matrix view.

TBs[Amount(Local)] - the local transaction value

TBs[FX_lookup_toUSD] - the fx conversion pair for that company 

In a separate table we have the FX rates

FXRATES[Rate] eg 1.25359262

FXRATES[FXLOOKUP] eg 2024-01-31

FXRATES[FXLOOKUPDATE] eg  ClosingGBPtoUSD

It is fine to create a new Calander table if necessary.

Being open I don't know how to do it efficiently so there is one measure that can do everything (eg cumulative sum by entity and convert to USD using the correct month end closing rate for the currency of the entity and depending on the month end column in the matrix).

Maybe it is not possible but I would like to think there maybe a better way to do this than creating several columns for each month end.

Worth noting we do not currently have an option to change the output of the ERP (eg a built in consolidation) alas.

[v-sathmakuri]

Hi @jhilton ,

I hope the information provided is helpful. Feel free to reach out if you have any further questions or would like to discuss this in more detail. If responses provided answers your question, please accept it as a solution so other community members with similar problems can find a solution faster.

Thank you!!

[v-sathmakuri]

Hi @jhilton ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you!!

[v-sathmakuri]

Hi @jhilton ,

May I ask if the provided solution helped in resolving the issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you!!

[AilleryO · Accepted solution]

Hi,

You could have another approcah to it :

Summarize by conversion rate and month, not only by month, as some months might have the same conversion rate. You can save a lot of calculation by doing this especially if you have many months in your data.

You might find inspiration on this page :

https://www.daxpatterns.com/currency-conversion/

Hope this helps