Can´t get a Balance Sheet to work properly with aggregated data

[Geojoel]

Good morning,

I started with PowerBI some weeks ago,and by looking youtube tutorials and this forum, I have created some Visuals and informs,but I´m still a novice in this.

At the moment, I´m stuck creating the Balance Sheet. For that, I have the accounting movements (ledger entries) extracted from the ERP. 

Then,since I need the amount for each account to be the aggregated until the date selected, I created the following measure:

Acumulado =

CALCULATE(SUM('BC:Movs_contabilidad'[Amount]),

ALLEXCEPT('BC:Movs_contabilidad','BC:Movs_contabilidad'[FECHA.PRESUP],'BC:Movs_contabilidad'[CUENTA Y EMPRESA]),

FILTER('BC:Movs_contabilidad','BC:Movs_contabilidad'[FECHA.PRESUP]<=MAX('BC:Movs_contabilidad'[FECHA.PRESUP])))

The "FECHA.PRESUP" value is the date of the line .With the formula,I wanted to say that PBI should sum all the values of that account and company (since we have two companies) until the date of that line.

Then, I extract the 4 first digits of the account and create a conditional column in PowerQuery, to get the subcategory of each one depending on those digits. I also do the same with two other conditional columns: the category and the "Mass" (Assets,Liabilities and Equity).

All of this works perfectly fine to create a visual.

However,the problem is with 4 accounts that,depending on the balance they have,can go either to Assets or to Liabilities. To take this into account, I created a calculated column:

Balance Subcat nuevo = IF(and('BC:Movs_contabilidad'[4 DIGITOS]=5510||'BC:Movs_contabilidad'[4 DIGITOS]=5525,[Acumulado]<0),"3. Otras deudas a corto plazo",IF(and('BC:Movs_contabilidad'[4 DIGITOS]=5523||'BC:Movs_contabilidad'[4 DIGITOS]=5524,[Acumulado]<0),"Deudas con empresas del grupo y asociadas a corto plazo",'BC:Movs_contabilidad'[Balance-Subcategoría]))

Basically stating that if this 4 accounts have the "Acumulado" measure less than zero,they have to go to a liability subcategory,and if not, to assets (which is the standard for this accounts,since I put that in the conditional column I created before). I checked and it puts the correct category for each row of these accounts.

However,when I create the visual matrix, even if the "Acumulado" measure is less than 0, the accounts still go to the Assets, because the category and "Mass" were still in Assets (due to the conditional column). This is when the problems began,since when I replicated this formula to both of these categories, I got a "circular referency" error. After cheking a lot of tutorials, I thought I get the solution, by adding the ALLEXCEPT part in the Acumulado formula, creating an Index column and making it the primary key. With this, I didn´t get that error. 

But,when I tried to create the matrix, I realized it didn´t aggregate the values of the accounts, instead it put in the Assets the aggregated rows in positive, and in Liabilities the same with negative rows. 

Basically,either I get the correct level of aggregation but placed in the wrong "Mass",or I get the correct "Mass" but more desagregated,which doesn´t work for what I want since it creates a bigger total amount for categories that shouldn´t have that much.

I also tried to simply make the Subcategory formula and then link the category and subcategory with the columns of another table (that has these 3 categories),with this formula (since LOOKUPVALUE send me the "A table of multiple values was supplied where a single value was expected" error):

CALCULATE(FIRSTNONBLANK('BALANCE RESUMEN CATEGORIAS'[Column3],1),

FILTER(ALL('BALANCE RESUMEN CATEGORIAS'),'BALANCE RESUMEN CATEGORIAS'[Column1]='BC:Movs_contabilidad'[Balance Subcat nuevo]))

But still didn´t work (actually it messed for some reason the values a lot and not even the other accounts were right)

So, any ideas on how I can solve this and have the accounts´ values aggregated until the selected date,and depending on the value (less or more than 0),clasify them into the correct category,without having a "circular referency" error?