Forum Discussion

AlainB's avatar
AlainB
Frequent Visitor
7 years ago
Solved

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 trans...
General Comment
Need Help
Tips and Tricks
  • Mariusz's avatar
    Mariusz
    7 years ago

    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.
    Mariusz Repczynski

     

  • AlainB's avatar
    AlainB
    Frequent Visitor
    7 years ago

    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