calc balance from debit and credit

[mohd89ali]

Hi all, 

i want to create some reports from the trial balance. basically, I only have the debit and credit for each account in a practical month.

This is a sample Data of what i work with. 

Account	Type	SMH 101110 Stock Valuation Account SMH	SMH 101120 Stock Interim Account (Received) SMH	SMH 101130 Stock Interim Account (Delivered) SMH	SMH 101200 Account Receivable SMH
10/01/19	Debit	882.112		922.106	7726.3
10/01/19	Credit	927.815	876.941	940.035	7726.3
11/01/19	Debit	5502.055	6360.948	951.393	7069.4
11/01/19	Credit	5451.155	476.658	1213.626	7069.4
12/01/19	Debit	1140.251		635.499	6345.95
12/01/19	Credit	634.449	1138.361	940.951	6345.95
01/01/20	Debit	1051.178		1323.25	8404.292
01/01/20	Credit	1326.692	1048.723	1325.066	8404.292
02/01/20	Debit	325.404		811.178	5257.46
02/01/20	Credit	815.807	298.938	811.178	5257.46
03/01/20	Debit	223.631		357.881	2443.36
03/01/20	Credit	360.137	222.627	357.881	2443.36
04/01/20	Debit	912.719		203.752	1248.94
04/01/20	Credit	203.752	912.719	203.457	1248.94
05/01/20	Debit	852.886		788.035	4857.3
05/01/20	Credit	826.584	843.212	782.248	4857.3
06/01/20	Debit			20.581	112.3
06/01/20	Credit	20.581		12.275	112.3

First of all, I want to calc the balance of each account. by default, PowerBi would sum the debit and credit which is wrong, I want the balance to be debit minus credit. 

one way is to multiply the credit values by -1 to make them negative. 

or by adding a balance row with each date that will calculate debit - credit. 

could you help me with that pleases? 

[MFelix · Accepted solution]

Hi @mohd89ali ,

Select the first and second column and then unpivot all others:

Now add the following measure:

Balance =
CALCULATE (
    CALCULATE ( SUM ( 'Table'[Value] ); 'Table'[Type] = "Debit" )
        - CALCULATE ( SUM ( 'Table'[Value] ); 'Table'[Type] = "Credit" );
    FILTER ( ALL ( 'Table'[Date] ); 'Table'[Date] <= MAX ( 'Table'[Date] ) )
)

Then just use it in your visualizations.

You should create a calendar table to use on your calculations instead of the actual table, but it also works as you can see.

Please see attach PBIX file.

I had to make some change on the date column due to my regional settings in your case you can use your date column as no need for the additional steps before the unpivot ( I have marked them as not needed).

[mohd89ali]

thank you very much for your help. 

I have one more issue, the sales formula is the other way around, its credit minus debit.

I have created a separate measure filtered by the account that calc the value and multiplies it by -1. 

Sales =
[Balance for SMH 200000 Product Sales SMH] * -1

is there a way to directly change it from the balance measure?

[MFelix]

Hi @mohd89ali ,

Balance for SMH 200000 Product Sales SMH

Based on my experience the best way is to do one of two things:

1. Change the signal on the query editor for that account
2. Split this into auxiliary measures:

Correct balance value = IF(SELECTEDVALUE('Table'[Account])="Balance for SMH 200000 Product Sales SMH";-[Balance];[Balance])


Final_Calculation = SUMX('Table';[Correct balance value])

Then use the final calculation on your calculations and settings.

[mohd89ali]

Hi @MFelix , 

i could not make your measure work with me. 

download pbix  

[MFelix]

Hi @mohd89ali ,

Your measure should be:

Correct balance value = IF(SELECTEDVALUE('Table'[Account]) IN { "SMH 200000 Product Sales SMH" },- [Balance],[Balance])