Forum Discussion
Calculate Account Balance with missing Transaction based Balance
Hi Everyone,
I am trying to calculate the missing Account Balance per Transaction, as the CSV Export I get looks like the table below:
I only get the balance once per day and not for every transaction.
fTableTransactions Date Debit Credit Balance Category Person 01.01.2019 -10.00 a Person 1 01.01.2019 -20.00 b Person 2 01.01.2019 100.00 c Person 2 01.01.2019 -40.00 1000.00 d Person 1 02.01.2019 -50.00 e Person 2 02.01.2019 -40.00 f Person 2 02.01.2019 50.00 960.00 g Person 1 02.01.2019 -20.00 h Person 1 02.01.2019 -10.00 930.00 i Person 1 04.01.2019 -30.00 a Person 2 04.01.2019 -100.00 800.00 e Person 1
How can I transform the Table so the Balance gets calulated for every row in Power Query like in the following Table?
fTableTransactions Date Debit Credit Balance Category Person 01.01.2019 -10.00 960.00 a Person 1 01.01.2019 -20.00 940.00 b Person 2 01.01.2019 100.00 1040.00 c Person 2 01.01.2019 -40.00 1000.00 d Person 1 02.01.2019 -50.00 950.00 e Person 2 02.01.2019 -40.00 910.00 f Person 2 02.01.2019 50.00 960.00 g Person 1 02.01.2019 -20.00 940.00 h Person 1 02.01.2019 -10.00 930.00 i Person 1 04.01.2019 -30.00 900.00 a Person 2 04.01.2019 -100.00 800.00 e Person 1
I tryed already with adding a -1 Index offet and create a new calculated column.
But the problem is, that I then cannot reference to the previous row in the same
Calculated Balance Column which I am trying to calculate.
Would be thankful for any hint.
Thanks already in advance for any answer or recommendation.
Reagards,
Alain
Hi AlainB
Please see the M code below
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjA0NLJR0lXUMDIJlXmpODoBKBOCC1qDg/T8FQKVYHXYcRpo4khA4jTB1QRYYGSBqT8erQNTFA0QfipWC4yghZhymmq1Ix7DDCZQeUSsOrA6oIbJOlGYhMx+8mLCGVgV8HcmxYGoM4mRgaTJA1GOONPiMsOlCiwQLMSUWxIxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Debit = _t, Credit = _t, Balance = _t, Category = _t, Person = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Debit", Int64.Type}, {"Credit", Int64.Type}, {"Balance", Int64.Type}, {"Category", type text}, {"Person", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "calcBalance", each [Balance], Int64.Type ), #"Filled Up" = Table.FillUp(#"Added Custom",{"calcBalance"}), #"Grouped Rows" = Table.Group(#"Filled Up", {"calcBalance"}, {{ "tbl", ( _partition ) => let sort = Table.Sort( _partition, { { "Date", Order.Ascending}, { "Category", Order.Ascending } } ), addIndex = Table.AddIndexColumn( sort, "Index", 0, 1 ), addColumn = Table.AddColumn( addIndex, "RT", each [calcBalance] - List.Sum( Table.AddColumn( Table.SelectRows( Table.ReplaceValue( addIndex, null, 0, Replacer.ReplaceValue, { "Debit", "Credit" } ), let _ind = [Index] in each [Index] > _ind ), "RT", each [Credit] + [Debit] )[RT] ) ) in addColumn, type table [Date=date, Debit=number, Credit=number, Balance=number, Category=text, Person=text, calcBalance=number, RT=number] }} ), #"Expanded tbl" = Table.ExpandTableColumn(#"Grouped Rows", "tbl", {"Date", "Debit", "Credit", "Balance", "Category", "Person", "RT"}, {"Date", "Debit", "Credit", "Balance", "Category", "Person", "RT Balacne"}), #"Replaced Value" = Table.ReplaceValue( #"Expanded tbl", null, each [Balance], Replacer.ReplaceValue, { "RT Balacne" } ), #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"RT Balacne", type number}}) in #"Changed Type1"Let me know if you need any help.
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
6 Replies
Hi AlainB
Please see the M code below
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjA0NLJR0lXUMDIJlXmpODoBKBOCC1qDg/T8FQKVYHXYcRpo4khA4jTB1QRYYGSBqT8erQNTFA0QfipWC4yghZhymmq1Ix7DDCZQeUSsOrA6oIbJOlGYhMx+8mLCGVgV8HcmxYGoM4mRgaTJA1GOONPiMsOlCiwQLMSUWxIxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Debit = _t, Credit = _t, Balance = _t, Category = _t, Person = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Debit", Int64.Type}, {"Credit", Int64.Type}, {"Balance", Int64.Type}, {"Category", type text}, {"Person", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "calcBalance", each [Balance], Int64.Type ), #"Filled Up" = Table.FillUp(#"Added Custom",{"calcBalance"}), #"Grouped Rows" = Table.Group(#"Filled Up", {"calcBalance"}, {{ "tbl", ( _partition ) => let sort = Table.Sort( _partition, { { "Date", Order.Ascending}, { "Category", Order.Ascending } } ), addIndex = Table.AddIndexColumn( sort, "Index", 0, 1 ), addColumn = Table.AddColumn( addIndex, "RT", each [calcBalance] - List.Sum( Table.AddColumn( Table.SelectRows( Table.ReplaceValue( addIndex, null, 0, Replacer.ReplaceValue, { "Debit", "Credit" } ), let _ind = [Index] in each [Index] > _ind ), "RT", each [Credit] + [Debit] )[RT] ) ) in addColumn, type table [Date=date, Debit=number, Credit=number, Balance=number, Category=text, Person=text, calcBalance=number, RT=number] }} ), #"Expanded tbl" = Table.ExpandTableColumn(#"Grouped Rows", "tbl", {"Date", "Debit", "Credit", "Balance", "Category", "Person", "RT"}, {"Date", "Debit", "Credit", "Balance", "Category", "Person", "RT Balacne"}), #"Replaced Value" = Table.ReplaceValue( #"Expanded tbl", null, each [Balance], Replacer.ReplaceValue, { "RT Balacne" } ), #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"RT Balacne", type number}}) in #"Changed Type1"Let me know if you need any help.
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.If there is an M solution, ImkeF will know what it is. If you can do DAX, you should be able to use EARLIER. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586
Hi Greg_Deckler
Thanks for the hint...but as I am not able to reference to the table that I am calculating, I do not see how EARLIER should work in my scenario. Nevertheless I stumbled over another of your articles "For and While Loops in Dax", I wonder if that could be the solution for my scenario...as I have the issue, that I have missing values. And as far as I understand it, with the loop you are calculating "virtual table" and bring the values back in your original table (sorry I am a newbie). But it seems like a littlebit overkill for my scenario.
Today I tried it with LOOKUPVALUE but here I had the same issue that I cannot reference to the Column (previous row) that I am calculating.Calculated Balance = IF( Table[Index] < 1;
Table[Balance];
LOOKUPVALUE(Table[Balance]; Table[Index]; Table[Index]-1) + Table[Debit] + Table[Credit]
)Here I am able to reference to another coloumn/value in the previous row (but not to the one that I am creating with "Calculated Balance"...but as soon as I have an empty value in my original blance table I only get the the SUM of [Debit] + [Credit] which is logical.
The other idea that came into my mind a few minutes age....
....why not calcualating for every row the complete [Credit] + [Debit] and always calculate back all previous Transactions? Not shure if this is possible?! :-SIf non of these solutions will work, I think I have to do the workaround over excel, which is not my prefered way.
Hope this gives you an idea what I have tried already.
Regards,
Alain
Hi Mariusz
Just an editing mistake in the sample tables.
Just corrected it.
This table is a transaction table from an account (basically how it exports to the .csv).
The Person and Category is later used for relations, filtering and messures.Regards,
Alain
