User Profile
jhilton
New Member
Joined 1 year ago
User Widgets
Contributions
Cumulative sums to month end dates, then apply a different FX rate for each month's cumulative sum
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.Solved686Views0likes4Comments
Data Privacy
Microsoft Fabric Community and Privacy
To learn more about how we manage your data, please review the Microsoft Fabric Community Data Privacy guide.