Can anyone point me in the direction of good content covering a balance sheet with multi companies and therefore multi currencies with the functionality to convert into any selected consolidation currency?
Creating the P&L is fine as i can itterate down the transactions using Sumx and convert the line at the exchange rate for that month dependent on the consolidation currency i have selected.
However, the balance sheet is melting my brain when it comes to consolidating all the companies into the consolidation currency i have selected.
I think Iāve got a resource that will help you. I just read/reviewed an excellent new book by Microsoft MVPs Henk Vlootman and Michiel Rozema titled āExtreme DAXā.
In it, they focus on applying advanced DAX techniques to common, specific business problems. They have an entire chapter on dealing with Intercompany Business that I think will help you build the model and the measures to meet your requirements. Hereās the description of that chapter:
How do you review books? Do you plan on reading each chapter different days or do you speed read through them and find which chapter might be useful. By the way I am ordering this book now. Did not know it existed.
My issue is not intercompany related, we have a consolidation adjustment company that we post intercompany adjustements to which then flows into PBI so i do not have to manipulate Intercompany.
My issue is consolidating varions entry lines which are made up of multi currencies into one consolidation value for the balance sheet. The P&L i can do but the Balance sheet is melting my brain.
So for example i have transactions for 3 different companies UK, USA and Canada, each of the companies transactions come out of the system in the companies entity currency so UK in GBP, USA in USD and Can in CAD. I need to be able to consolidate all the lines into whatever consolidaiton currency the user has picked via the slicer.
The P&L in an easy sumx across the whole table and apply the exchange rate for each line based on the line transaction date. I drop this into a matrix table and all works.
My issue is with the Balance sheet, each month in the matrix is a sum of all the values up to that month end date converted at the month end exchange rate. If all the lines were in GBP and i wanted to convert to a consolidation currency of USD that would be easy i would sum all the values up to that month end date and then convert at the GBP to USD month end exchange rate. But my lines are a mix of GBP, USD and CAD and so i just canāt get this to work.
If you have a pbix file that will be great but from my experience there are 2 ways of doing it either using power query or dax
With Power query what I do is I have a table for all currencies in all countries for the different month and periods and the balance sheet data I duplicate it for the different countries unique currency and I use merge function in power query to link the country currency and date to the desired country and date I need. Then in dax I create a separate currency dim table and link to the currency fact table with all my countries and currencies so when I filter one currency and country I get the desired exchange. It is difficult to explain unless you see a real example and the end product.
Hi @BCS. This issue also presented me with a challenge (no pun intended) during the preparation of my eDNA Challenge 9 submission, and you can find it in post 23 of that thread (replying on my phone right now and linking doesnāt seem to be working ā¦ Iāll update this post when Iām back at the computer). Hopefully thereās gold you can find in the various submissions to C9.
Greg
See scaled down PBIX file, Iāve stripped out everything so i can share so itās just a shell.
If we concentrate on one account code 2070 which i have filtered in the BS tab, the correct values should be as per this Excel snip, I have also attached the Excel file which feeds the PBIX file.
One thing i should point out, the exchange rates come out the system as rates against USD so thatās why i have to do a couple of steps for my rates.
@Greg I do actually have your submission file for the currency challenge. I was trying to see how you did your power query transformations for this but you have additional data coming from your Excel file that was not on the original data file for the challenge so when i try and look at the power query workings the file falls over as i donāt have these additional tables.
Hi @BCS. I doubt I included the 2 extra files, and for your convenience Iāve attached them. (DAX Studio is always my first go-to when I want to extract the data tables from any PBIX). Hope they help.
Greg Currencies.xlsx (23.5 KB) multiple-series-sd-2019-01-01-ed-2020-11-06.csv (116.0 KB)
Thanks @Greg for the files but iām not sure your issue and my issue are the same. I think that what you are calculating is in affect still a P&L transaction value which is the value on that date x the exchange rate for that date. I could be wrong though.
My issues comes where i have to calculate the Balance sheet as at date value which i can do in Excel but not in PBI.
So as per my example files and snip above for the UK account code 2070:
Entity currency is GBP and all the UK transactions are in GBP
Consolidaiton currency selected via slicer is USD
Jan = sum of entity value up to 31st Jan is 8.2k GBP, convert at month end rate of 1.37 = 11.3k USD
Feb = sum of entity value up to 28th Feb is (9.3k) GBP, convert at month end rate of 1.39 = (13.4k) USD
If i only select 1 entity then i can actually calculate the above values, but when i show all entities at once, so consolidate the values so you can see the group position it doesnāt work.
I basically have 3 calculations each month as i have 3 different entity currencies that i am trying to consolidate into the consolidation currency as per the slicer. If we look at what Feb would look like as Canada doesnāt have any transactions in Jan, the Dax needs to calculate the following:
UK Feb = sum of entity value up to 28th Feb is (9.36k) GBP, convert at month end rate of 1.39 = (13.4k) USD
USA Feb = sum of entity value up to 28th Feb is 10.1k USD, convert at month end rate of 1 = 10.1k USD
CAN Feb = sum of entity value up to 28th Feb is (6.7k) CAD, convert at month end rate of 0.78 = (5.2k) USD
So the total for the group in my February column in the consolidation currency of USD would be (8.5k) USD.
Hi @BCS. Iāll leave the financial issues to other more qualified members; Iām an engineer by training and have only a rudimentary exposure to accounting.
Greg
This might be a really stupid question. Why isnāt your general ledger system doing this automatically? It seems to be a lot of work for nothing where the system can do it.
Hi @ambepat see attached file with the fix, the measure that sorted my issue is below. Basically i had to make a table with the sum of the individual currencies and then apply the correct month end exchange rate per currency to that table, sounds simple when i write it out but it turned me inside out for days so thanks for the links.
Var MaxDate = MAX(Dates[Date])
Var AmountinCurrency =
ADDCOLUMNS(
VALUES(Consol_GLE[Entity_Curr]),
āAmountOriginalCurrencyā, CALCULATE(SUM(Consol_GLE[Entity_Value]), Dates[Date] <= MAX(Dates[Date])),
āClosingEXRateā, āMeasure Tableā[ExRate New])
Var Result = SUMX(AmountinCurrency, [AmountOriginalCurrency] * āMeasure Tableā[ExRate New])
The finance system does not have a consolidation entity which would consolidate the entities so i have a group perspective in one currency hence my issue. I agree that it would be better if this was set-up but itās not so i needed a work around.
