Balance Sheet Table Template

Hello all of you
I’ve watched this video from Sam :
https://www.youtube.com/watch?v=bmf4_zXCXbs&ab_channel=EnterpriseDNA
and it’s a great technique. I’ve used it to do my P&L.
I’m not an expert of accounting but i’m trying to do the balance sheet and as far as i know, here in my country at least, the items from Assets are the DEBIT values and from Liabilities are CREDIT values.

My question is : since i have only one measure at the bottom of this long measure, that calculates the debits, how could i add another measure that could calculate the credits but only for the Liability section?
Thank you all of you guys
Pedro

Hello @pedroccamara,

Thank You for posting your query onto the Forum.

Just to confirm, in all the countries Assets are considered as "Debit values and Liabilities are considered as Credit values.

Well if you follow the @sam.mckay’s video carefully. He has calculated the sub-totals for each sub-category i.e. Current Assets, Fixed Assets, Other Assets, Current Liabilities, Long - Term Liabilities, etc. and then all those measures has been consolidated under the one single master measure wherein the last two line items of the measure will bring the values for each individual line items.

Now, to get you started with the working of the Balance Sheet I’ll be attaching the working of my PBIX file for the reference. Can you also attach the working of your PBIX file as well so that the we could assist you in a better way?

If you need anything just give a shout.

Hoping you find this useful. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Income Statement and Balance Sheet - Harsh.pbix (859.9 KB)

Hi @pedroccamara

Generally, the filter table, i.e. Chart of Accounts will have the following columns,

  • GL (General Ledger) ID / #
  • GL Description
  • GL Group Level 1 – in its simplistic form, this could be Balance Sheet or Income Statement, that is, a GL ID is either one of these
  • GL Group Level 2 – splits the balance sheet group into Assets and Liabilities
  • GL Group Level 3 – split Liabilities into Current Liabilities and Long-Term Liabilities

And yes, liabilities are generally credits (i.e. -ve values) and assets are debits (i.e. +ve values).

The fact table, i.e. GL Summary, will have the following columns – at a minimum,

  • GL ID
  • Month & Year
  • Value

Around 6 mins into the video you have a B/S Values measure that links each total / sub- total (in the balance sheet template) to a measure – i.e. Current Liabilities, Long-Term Liabilities, etc.

As with Power BI there are a few different ways to achieve this.

Here is one way,

GL Value = SUM ( ‘GL Summary’[Value] )

Hope this help.

Manoj

2 Likes

Guys,
You’re the best. Let me read (digest) your answers this weekend (away from my laptop and forbidden to work) and i’ll get back to you on this asap!

Ok guys! I’ve manage to borrow my son’s laptop and i did read your answers, although “i don’t want to do it” as you say but my way. If i’ll do it your way, i won’t learn, you know?
So what i did, i got this :


I know that my values after CAPITAL PRÓPRIO E PASSIVO are wrong. My measure, so far, is :

My support table balance is like this :

Support Table Balance

I’ve merge this table with the General Ledger table. Now, my idea is to add a conditional column, something like this: if it’s Ativo then Debits, else credits. What do you guys think? Not the best approach?
I’ve noticed that the Balance sheet data has values in it. It seems to be a static excel file. Don’t know how you guys did it, but i believe maybe this should also be considered by me, if only i could know how to do it, you know?
So, this solution or my solution? What do you guys, think?
Can’t thank you enough for trying to help me @Harsh and @M_K
Best regards
Pedro

1 Like

Hi @pedroccamara,

My personal opinion on conditional formatting if debit or credit on an account is not a good idea. The financial people reviewing the statement will know if its a debit or credit. (you would confuse the issue) If you were comparing the ending balances from one year to another year then you could use conditional formatting. for example if increase then blue formatting if decrease then red. You have to understand the type of balance sheet account you are dealing with under conditional formatting.

You could use up and down arrows on accounts as conditional formatting that would help more then color formatting.

I come from a financial point of view, just be careful on financial statement presentation.

this is my opinion.
thanks
Keith

2 Likes

Yes it’s a balance sheet to compare years and also, the conditional column is like, if this column has this designation than give the debit amount otherwise the credit amount. There’s 4 or 5 account numbers, that can have both credit and debit values. That’s why.
Do you agree with my procedure @Keith?

Hi @pedroccamara,

it depends on the account name/account,

Assets (debit value in gl system) if increase in value from one year to another then up arrow (good) else down arrow (bad)
ie 1999 equal 200 …2000 equal 180 decrease of 20 result is down arrow (bad)

Liability (credit value in gl system) if increase in value from one year to another than up arrow (bad) else down arrow (good)
IE: 1999 total is 100 and 2000 total is 200 increase of 100 this up arrow equal bad

Capital (credit value in gl systm) if increase in value from one year to another than up arrow (good) else down arrow (bad).
ie 100 (1999) to 120 (200) increase of 20…good up arrow

if you want, send me what you come up and i can review.

I hope this helps.
Keith

1 Like

Hello @pedroccamara,

Yup, you’re almost onto the right track. As you said that -

If it’s Ativo then Debits, else credits. What do you guys think? Not the best approach -

What you can actually do is rather than going for the category approach directly go for the sub-categories approach. That is firstly calculate the sub-totals for each sub-category - Current Assets, Current Liabilities and so on.

Then once the sub-totals are calculated for each of the sub-categories. Just go for the Totals for each of the category by adding up the totals of each sub-categories. That is -

Assets = Current Assets + Fixed Assets + Other Assets

This same approach goes for Current Liabilities as well.

Now, once you have built all these small chunk of measures. Go for the master measure at the end. That will incorporate all these measures.

But before you proceed further, as you said -

There’s 4 or 5 account numbers, that can have both credit and debit values.

I didn’t understand this statement. Is is the naming convention or something else? Because if it’s a same naming convention i.e. same type of account name appearing on the Assets as well as the Liabilities side. It will be very much important to correct that. I’m providing a link of the solution where how the same type of convention affected the Balance Sheet totals. Although the query started with the formula error but ended with the naming convention problem as well.

If you need anything just give a shout and will always be happy to help.

Have a great weekend. :+1:

Hoping this link helps you, if that’s the case. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

1 Like

Hey @Harsh! What a great help you gave me. Thanks a lot!!
I believe i came up with a “mixed” solution, between all these answers. Let me know if you agree with me, please.

  1. I’ve create a new column with those specific “terms” that account name and credit value were <> 0. I’m talking about the terms that we have the same name but for assets. Add 2 more for Category and Type Bal.
  2. Merge this table with the support table balance, the one that has only, account ID, Type Bal, Category and Sub Category,
  3. Since now you have 6 columns you have to choose the 3 you need, with a conditional column.
  4. Finally, Add a column (Balance Values) where if it’s assets, debit values, if blank, 0, else, credit values.
    It’s not the same way you’d do it for sure, but i had to come up with a solution for tomorrow morning, 14 hours from now.
    Again @Harsh, thank you so so so much for your incredible patience to help me.
    Best regards
    Pedro
1 Like

Hello @pedroccamara,

You’re Welcome. :slightly_smiling_face:

I’m really glad that I was able to help you.

So, now coming back to the query that you’ve asked. Here’s my answer pointers wise -

  1. Well with the first point of yours, I actually may agree/disagree depending upon the circumstances. So in my case what I would have done is edit the those names which contains the same naming convention within the same column. So let’s say I’ve - an Account Name as “Others” under the Assets side as well as under the Liabilities side. So what I’ll do is change the change as “Other - Assets” and “Other - Liabilities”. So by doing this I’ll save an effort of creating an extra columns. And another most, most important reason is the more columns you add, the deeper the columns/categories will get. That is, let’s say, right now you’ve three columns - Accounts, Sub-Accounts and Balances. By creating columns just to correct the naming conventions. Now, you may have - Accounts, Sub-Accounts, Sub-Sub-Accounts (or whichever name you may give in your case). And then more measures you’ll have to create for sub-totals in order to have one single total for Assets. For example -

In previous case, it the measure was as follows -

Assets = Current Assets + Fixed Assets + Other Assets

Here, you’ve to write only one measure for Other Assets. Now, if you add more columns the Other Assets column will get more deeper and deeper due to additional columns so then you’ll have to write more sub-measures to get the sub-totals for “Other Assets

Note: It’s just an example that I’ve created in this scenario, you may different names in your accounts where the actual problem lies. But the concept/logic will absolutely remain the same.

  1. For the second point, when you try to merge this tables with each other. The most important thing to remember over here is that, both the tables should contain same number of columns. (I presume you’ll use “UNION()” function to join/merge both these tables).

  2. For third point, actually I was not able to understand the statement here. Where and how the conditional columns will be used? Out of which 6 columns 3 columns will be put to use?

  3. For fourth point, if you’ve created measures and sub-measures as I’d said i.e.

Assets = Current Assets + Fixed Assets + Other Assets

And

Equities and Liabilities = Owner’s Equity + Shareholder’s Funds + Current Liabilities + Long-Term Liabilities + Other Liabilities

Then just put them in one master measure and these will be good to go. But as you said -

Finally, Add a column (Balance Values) where if it’s assets, debit values, if blank, 0, else, credit values.” - If you’ve a template then how will you put /move the figures from the Balance Sheet to the Template? But if you’re not using a template and just showing values in a simple “Table” or “Matrix” then this option is I guess good to go. (But still adding a column should be a last option if this can be done via measure, just a thought. Not a compulsory option.)

I do understand that showing values as per the Financial Format is a very tedious process. But if you feel that the option you’ve will work (atleast for now) since you’ve to present the report in a given time frame then go for it. Absolutely not a viable option to make last minute changes because if the formulas are written hastily there could be a chance of committing an error and the numbers may not portray true picture and ultimately the report might collapse.

But for the next time, I guess you can go definitely go the points that I’ve suggested (If it seems viable to you).

I wish you all the very best for the project that you’re working on. And hope that you achieve the desired results or analysis that you’re looking for. :slightly_smiling_face:

Still if you need any help from my side just give a shout and I’ll more or more than happy to assist you anytime. :+1:

And lastly, my sincerest thanks to you as well for your kinds words of appreciation and recognizing the efforts. Thank you, my friend. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

3 Likes

Hey @Harsh
I’m very sorry for this late late reply but, really, i had some urgent things to do.
First of all, all that i’ve done, i’ve did it in my fact table. All the data there.

  1. I think i did what you’ve suggested, that is, to add OTHERS assets and OTHERS Liability. From one side, i want only the debits and on the other the credits.So, i needed 5 more columns, each one made with a conditional column like this, if credit > 0 and account id is 11, 12 or 25 than column name.
  2. Then i’ve join (merge) all 5 columns into one (Sub Category column). I’ve add a Category and Main Category for each new Sub Category.
  3. Now i’ve merge with list of all accounts. The one that have account id, account name, sub cat, cat and main cat.
  4. Now i have 6 columns (3 extra (sub cat, cat and main cat) and through 3 conditional columns, i choose the right 3 columns.
  5. Finally, i’ve add a column (Balance Values) where if it’s assets, debit values, if blank, 0, else, credit values.
    Please let me know if i wasn’t clear enough. This way, as my first time to do a balance sheet, it seemed simple to me, you know?
    So, my measure to add at my support balance is :

BS Values =
VAR CurrentItem = SELECTEDVALUE(‘Support Balance’[Descr2])
RETURN

SWITCH( TRUE(),
CurrentItem = “Total Ativo não corrente”, [Total Ativo não corrente],
CurrentItem = “Total Ativo Corrente”, [Total Ativo Corrente],
CurrentItem = “Total do Ativo”, [Total do Ativo],
CurrentItem = “Resultado líquido”, [Net Result (RL)],
CurrentItem = “Total Capital Próprio”, [Total Capital Próprio],
CurrentItem = “Total Passivo”, [Total Passivo],
CurrentItem = “TOTAL CAPITAL PRÓPRIO E PASSIVO”, [Total Capital Próprio e Passivo],
CurrentItem = “”,"",

CALCULATE([Balsheet Values], FILTER(‘General Ledger’, ‘General Ledger’[Sub Categoria] = CurrentItem )
))

and my report :

And of course i also have 4 columns, this year, LY, Diff and Diff%, which by the way, the measure above (on the CALCULATE zone) for last year, as well for the difference, didn’t work with the [Balsheet values]-Balsheet values LY]. Thanks to you and the file that you’ve send me, i had to do like this:

CALCULATE([Balsheet Values] , FILTER(‘General Ledger’, ‘General Ledger’[Sub Categoria] = CurrentItem )) -
CALCULATE(
CALCULATE( [Balsheet Values],
FILTER( ‘General Ledger’, ‘General Ledger’[Sub Categoria] = CurrentItem ) ),
DATEADD( ‘Tab Dates’[Date], -1, YEAR ) ))

Didn’t understand why but it works. The other way didn’t.
Again, @Harsh thanks a lot for your precious help
Please stay safe and healthy
Pedro

1 Like

Hello @pedroccamara,

You’re Welcome. :slightly_smiling_face:

I’m glad that I was able to help you.

And more than that I’m really happy that our discussion provided you the fruitful results that you’re really looking for. So absolute kudos and cheers to you. :+1: :clap: :clap:

Thanks and Warm Regards,
Harsh

1 Like

Well done here Harsh. Tremendous value add to this thread. Amazing stuff.

3 Likes

Hello @sam.mckay Sir,

Thank You for recognizing the efforts.

It’s always a great feeling and a huge morale booster for us when we receive a word of appreciation from you. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

1 Like

hi,

imho, for cumulative amount items (as for profit & loss items), folloing dax formula works fine

T PL Value =
CALCULATE(
SUM(‘Data-PL’[PL Value]),
TREATAS(
VALUES( ‘Calendar’[Date] ),
‘Data-PL’[Date]
)
)

however, i don’t think such formula can be applied for balance sheet item as the value shouldn’t be cumulative but as of the last date. if my argument is correct on the balance sheet value matter, can i get a help on writing the right dax formula for that.

tks & krgds, -nik

Hi @nikahafiz,

you might want to start a new topic so you can be provided with best solution as possible as you are on someone else topic string with a solution already applied.

thanks
keith

tks, keith.

i have created a new topic in Non-cumulative Item in Balance Sheet Table Template.

krgds, -nik