cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper III

## Balance sheet modeling

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.

1 ACCEPTED SOLUTION
Resolver I

sorry, I did not fully understand this part in my first answer.

``````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
)``````
7 REPLIES 7
Resolver I

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])
Helper III

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

Resolver I

sorry, I did not fully understand this part in my first answer.

``````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
)``````
Helper III

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

Helper III

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

Resolver I

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.

Helper III

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors