Calculate Account Balance with missing Transaction based Balance

[AlainB]

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

[Mariusz · Accepted solution]

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.

[AlainB]

Hi @Mariusz 
First of all thank you for you work on my problem.

I will give it a try an share the outcome asap.

Regards,

Alain

[Mariusz]

Hi @AlainB 

Why Person and category does not impact the balance and why you moved 50 from balance in tbl 1 to credit in tbl 2?

Best Regards,
Mariusz

Please feel free to connect with me.

[AlainB]

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

[Greg_Deckler]

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/3395...

[AlainB]

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?! :-S

If 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