Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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).
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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).
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Portuguêsthank 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?
Hi @mohd89ali ,
Balance for SMH 200000 Product Sales SMH
Based on my experience the best way is to do one of two things:
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @mohd89ali ,
Your measure should be:
Correct balance value = IF(SELECTEDVALUE('Table'[Account]) IN { "SMH 200000 Product Sales SMH" },- [Balance],[Balance])
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |