Balance sheet modeling

[FrancescoSCP]

Hi everyone,

I have an issue with data modeling.

I have a table of accounting movements and another table containing the reclassified balance sheet schema. The link between these two tables is the account code.

There are duplicate accounts because, based on the balance of the movements for that period, the data should be displayed on one account rather than another.

For example, the "bank" account may be displayed under "liquidity" if the balance is positive, while under "liabilities" if the balance is negative.

Expected behaviour is this:

As you can see, in month 1 value of Acc. Code "10200" is -50 so it is displayed only in Liabilities. On month 2 the value is +70 so it is displayed on Liquidity and so on. The total value, for the three selected months, is -50+70-30 = -10 so it's displayed only on liabilities.

The balance value depends on the user period selection and i can't precalculate it because he can select any combination of month-year.

I'm not sure how to handle this with a many-to-many relationship.

I've tried using measures, but the subtotals are incorrect.

Do you have any ideas?

Here you can find a .pbix files with demo data and what i've done
https://we.tl/t-1F9zEUGvxt

Thank you.

[AMeyersen]

Hi @FrancescoSCP ,
I'd remodel the data structure to avoid complex relationships and DAX statements.

1. Create surrogate keys in table [Balance Sheet] to have a unique identifier for both [10200 - Liquidity] and [10200 - Liabilities]
2. Transform 'Data'[Acc Code] to match the new surrogate keys. Distinquish between positive and negative values.
3. Have a simple 1:n relationship and a simple measure SUM('Data'[Value])

[FrancescoSCP]

Hi @AMeyersen ,

i can't do this because i don't know on single line on the fact table if the balance of that Acc Code is negative or positive based on the user selection.

Regards,

Francesco

[AMeyersen · Accepted solution]

Hi @FrancescoSCP ,

sorry, I did not fully understand this part in my first answer.
You can adjust your DAX formula to calculate the correct subtotals:

Sum of value = 
VAR _fullSum =
    SUM ( Data[Value] )
VAR _partialSum = CALCULATE(SUM(Data[Value]), 'Balance Sheet'[Sign] <> "")
VAR _sign =
    IF (
        AND (
            CONTAINS ( 'Balance Sheet', [Sign], "A" ),
            NOT ( CONTAINS ( 'Balance Sheet', [Sign], "D" ) )
        ), "A",
        IF (
            AND (
                CONTAINS ( 'Balance Sheet', [Sign], "D" ),
                NOT ( CONTAINS ( 'Balance Sheet', [Sign], "A" ) )
            ), "D" ))
RETURN
    SWITCH (
        _sign,
        "A", IF(_partialSum > 0, _fullSum - _partialSum, _fullSum),
        "D", IF(_partialSum < 0, _fullSum - _partialSum, _fullSum),
        _fullSum
    )

[FrancescoSCP]

Hi @AMeyersen, 

i need a different behaviour on column total, it has to be the sum of all months.

So for example, total of Liquidity has to be 70, total of Liabilities has to be -80. And also subtotals, always on column total, has to be correct. For example Voice A1 total has to be 100+150+50 = 300.

The behaviour in the single month is perfect.

Any ideas?

Regards,

Francesco

[FrancescoSCP]

Hi @AMeyersen , It seems to be working. Could you explain to me the formula in the various steps?

[AMeyersen]

Hi @FrancescoSCP , sure 🙂

• _fullSum => sum without any adjustments
• _partialSum => sum of signed accounts (A/D)
• _sign => If the current filtercontext contains a signed account (A/D) of one type but not of both, the value needs an adjustment.
• SWITCH => Sign type "A": If the sum of signed accounts is > 0 we need to exclude this value. Sign type "B": If the sum of signed accounts < 0 we need to exclude this value

The last part sets the value on signed accounts conditionally to zero and does the same adjustment to the subtotals.

If a sub(total) contains accounts of both types or no signed accounts at all there's no need for adjustments and therefore we can use the _fullSum.

[FrancescoSCP]

Perfect thanks! I will test this soon on the full model!