none
How to create opening balance from previous day closing balance dynamically RRS feed

  • Question

  • Hello Everyone,

    I want to create opening balance of items from previous day balance dynamically.

    For example,

    Date Item Opening IN Out Closing
    5-Sep-19 A 10 2 5 7
    5-Sep-19 B 3 4 2 5
    5-Sep-19 C 2 6 8 0
    5-Sep-19 D 7 9 2 14
    5-Sep-19 E 10 10 2 18
    5-Sep-19 F 10 4 6 8
    5-Sep-19 G 8 4 8 4
    6-Sep-19 A 7 1 5 3
    6-Sep-19 B 5 1 1 5
    6-Sep-19 C 0 4 3 1
    6-Sep-19 D 14 3 8 9
    6-Sep-19 E 18 1 5 14
    7-Sep-19 A 3 6 6 3
    7-Sep-19 B 5 3 2 6
    7-Sep-19 C 1 2 2 1
    7-Sep-19 D 9 3 4 8
    7-Sep-19 E 14 3 10 7

    If i put G in Sep 8, 2019 then opening balance should be 4.



    Hayder Alee

    Thursday, September 5, 2019 5:36 PM

Answers

  • Example (in readable longform): 

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        changedType = Table.TransformColumnTypes(
            Source,
            {
                {"Date", type date}, 
                {"Item", type text}, 
                {"Opening", Int64.Type}, 
                {"IN", Int64.Type}, 
                {"Out", Int64.Type}, 
                {"Closing", Int64.Type}
            }
        ),
        groupedRows = Table.Group(
            changedType, 
            {"Item"}, 
            {
                {
                    "Tables", 
                    each Table.AddIndexColumn(_, "Index"), 
                    type table [Date = date, Item = text, Opening = number, IN = number, Out = number, Closing = number]
                }
            }
        ),
        addedBalance = Table.TransformColumns(
            groupedRows, 
            {
                "Tables", 
                (i) =>  if Table.RowCount(i) > 1 then
                            Table.ReplaceValue(
                                i, 
                                each [Opening], 
                                each if [Index] > 0 then i[Closing]{[Index] - 1} else [Opening],
                                Replacer.ReplaceValue,
                                {"Opening"}
                            )
                        else i
            }
        ),
        combinedTables = Table.Combine(addedBalance[Tables]),
        sortedRows = Table.Sort(combinedTables,{{"Date", Order.Ascending}, {"Item", Order.Ascending}}),
        removedIndex = Table.RemoveColumns(sortedRows,{"Index"})
    in
        removedIndex

    • Marked as answer by Haider Alee Saturday, September 7, 2019 2:31 AM
    Thursday, September 5, 2019 8:13 PM

All replies

  • Example (in readable longform): 

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        changedType = Table.TransformColumnTypes(
            Source,
            {
                {"Date", type date}, 
                {"Item", type text}, 
                {"Opening", Int64.Type}, 
                {"IN", Int64.Type}, 
                {"Out", Int64.Type}, 
                {"Closing", Int64.Type}
            }
        ),
        groupedRows = Table.Group(
            changedType, 
            {"Item"}, 
            {
                {
                    "Tables", 
                    each Table.AddIndexColumn(_, "Index"), 
                    type table [Date = date, Item = text, Opening = number, IN = number, Out = number, Closing = number]
                }
            }
        ),
        addedBalance = Table.TransformColumns(
            groupedRows, 
            {
                "Tables", 
                (i) =>  if Table.RowCount(i) > 1 then
                            Table.ReplaceValue(
                                i, 
                                each [Opening], 
                                each if [Index] > 0 then i[Closing]{[Index] - 1} else [Opening],
                                Replacer.ReplaceValue,
                                {"Opening"}
                            )
                        else i
            }
        ),
        combinedTables = Table.Combine(addedBalance[Tables]),
        sortedRows = Table.Sort(combinedTables,{{"Date", Order.Ascending}, {"Item", Order.Ascending}}),
        removedIndex = Table.RemoveColumns(sortedRows,{"Index"})
    in
        removedIndex

    • Marked as answer by Haider Alee Saturday, September 7, 2019 2:31 AM
    Thursday, September 5, 2019 8:13 PM
  • Hi Colin,

    Thanks for replying, so far it is working but i need calculation of closing balance dynamically.

    Only punch value column will be IN & OUT whereas opening and closing balance should work automatically.   

    With your solution Closing balance column doesn't count opening column to calculate closing.


    Hayder Alee

    Friday, September 6, 2019 4:35 PM
  • Hi Colin,

    Thanks for replying, so far it is working but i need calculation of closing balance dynamically.

    Only punch value column will be IN & OUT whereas opening and closing balance should work automatically.   

    With your solution Closing balance column doesn't count opening column to calculate closing.


    Hayder Alee

    I suppose that I was expected to deduce the above based on your initial post??? Unfortunately, I am very limited in my ability to extrapolate beyond what's been requested. :)

    At any rate, I don't see a solution to your request. For example, what criteria would be used to get the opening values for 5-Sep-19A to G if you are manually inserting values only in the IN and OUT columns?

    Friday, September 6, 2019 7:58 PM