none
How to create opening and closing balance dynamically RRS feed

  • Question

  • Hi there,

    Please help to create both opening and closing dynamically.

    For Example

    first criteria 

    closing column =opening +IN-Out

    Second Criteria

    Opening Column = closing

    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

    In 6-Sep-19 if i put any item name i,e A,B,C,D,E then opening column should bring previous day closing number whereas closing column should also work with 1st criteria.  


    Hayder Alee

    Saturday, September 7, 2019 2:55 AM

Answers

  • Hi Hyder,

    For the following code to work, one criterion must be met, i.e., The first occurrence of an item must have a manually inserted opening value. In your table, all opening balances for 5-Sep-19 must be entered manually. If there is an Item G on 6-Sep-19 (first time it appears), the opening balance must be specified, otherwise no closing value can be calculated, and used for the next occurrence of G (this point should be obvious though). 

    The code assumes that the Closing column is present in the table, but that the column is empty to begin with i.e. no manual entries are required, since all closing balances are calculated.

    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]
                }
            }
        ),
        addedBalances = Table.TransformColumns(
            groupedRows,
            {
                "Tables",
                (i) => List.Accumulate(
                    {0..Table.RowCount(i) - 1},
                    (i),
                    (accum, curr) =>
                    let 
                        addedClosingValues = Table.ReplaceValue(
                            accum,
                            each [Closing],
                            each [Opening] + [IN] -[Out],
                            Replacer.ReplaceValue,
                            {"Closing"}
                        ),
                        addedOpeningValues = if Table.RowCount(accum) > 1 then
                                                Table.ReplaceValue(
                                                    addedClosingValues,
                                                    each [Opening],
                                                    each if [Index] > 0 then  addedClosingValues[Closing]{[Index]-1} 
                                                        else [Opening],
                                                    Replacer.ReplaceValue,
                                                    {"Opening"} 
                                                )       
                                            else addedClosingValues
                    in
                        addedOpeningValues
                )
            }   
        ),
        combinedTables = Table.Combine(addedBalances[Tables]),
        sortedRows = Table.Sort(combinedTables,{{"Date", Order.Ascending}, {"Item", Order.Ascending}}),
        removedIndex = Table.RemoveColumns(sortedRows,{"Index"})
    in
        removedIndex

    • Marked as answer by Haider Alee Sunday, September 8, 2019 6:54 PM
    • Unmarked as answer by Haider Alee Monday, September 9, 2019 8:19 AM
    • Marked as answer by Haider Alee Monday, September 9, 2019 8:20 AM
    Saturday, September 7, 2019 6:27 PM
  • Null does not count in closing balance in above code. If there is null in IN and OUT column then closing column result is null as well. 

    That's because any arithmetic operation with null will always return null:

    Replace this line:

    each [Opening] + [IN] -[Out],

    With the following:

    each if [IN] = null and [Out] = null then [Opening]
         else if [IN] <> null and [Out] = null then [Opening] + [IN]
         else if [IN] = null and [Out] <> null then [Opening] - [Out]
         else [Opening] + [IN] - [Out],

    • Marked as answer by Haider Alee Wednesday, September 11, 2019 10:09 AM
    Monday, September 9, 2019 2:47 PM

All replies

  • Hi Hyder,

    For the following code to work, one criterion must be met, i.e., The first occurrence of an item must have a manually inserted opening value. In your table, all opening balances for 5-Sep-19 must be entered manually. If there is an Item G on 6-Sep-19 (first time it appears), the opening balance must be specified, otherwise no closing value can be calculated, and used for the next occurrence of G (this point should be obvious though). 

    The code assumes that the Closing column is present in the table, but that the column is empty to begin with i.e. no manual entries are required, since all closing balances are calculated.

    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]
                }
            }
        ),
        addedBalances = Table.TransformColumns(
            groupedRows,
            {
                "Tables",
                (i) => List.Accumulate(
                    {0..Table.RowCount(i) - 1},
                    (i),
                    (accum, curr) =>
                    let 
                        addedClosingValues = Table.ReplaceValue(
                            accum,
                            each [Closing],
                            each [Opening] + [IN] -[Out],
                            Replacer.ReplaceValue,
                            {"Closing"}
                        ),
                        addedOpeningValues = if Table.RowCount(accum) > 1 then
                                                Table.ReplaceValue(
                                                    addedClosingValues,
                                                    each [Opening],
                                                    each if [Index] > 0 then  addedClosingValues[Closing]{[Index]-1} 
                                                        else [Opening],
                                                    Replacer.ReplaceValue,
                                                    {"Opening"} 
                                                )       
                                            else addedClosingValues
                    in
                        addedOpeningValues
                )
            }   
        ),
        combinedTables = Table.Combine(addedBalances[Tables]),
        sortedRows = Table.Sort(combinedTables,{{"Date", Order.Ascending}, {"Item", Order.Ascending}}),
        removedIndex = Table.RemoveColumns(sortedRows,{"Index"})
    in
        removedIndex

    • Marked as answer by Haider Alee Sunday, September 8, 2019 6:54 PM
    • Unmarked as answer by Haider Alee Monday, September 9, 2019 8:19 AM
    • Marked as answer by Haider Alee Monday, September 9, 2019 8:20 AM
    Saturday, September 7, 2019 6:27 PM
  • Hello Colin,

    Many thanks for helping. This is exactly what i wanted. 


    Hayder Alee

    Sunday, September 8, 2019 6:58 PM
  • Hello Colin,

    Null does not count in closing balance in above code. If there is null in IN and OUT column then closing column result is null as well. 


    Hayder Alee

    Monday, September 9, 2019 8:28 AM
  • Null does not count in closing balance in above code. If there is null in IN and OUT column then closing column result is null as well. 

    That's because any arithmetic operation with null will always return null:

    Replace this line:

    each [Opening] + [IN] -[Out],

    With the following:

    each if [IN] = null and [Out] = null then [Opening]
         else if [IN] <> null and [Out] = null then [Opening] + [IN]
         else if [IN] = null and [Out] <> null then [Opening] - [Out]
         else [Opening] + [IN] - [Out],

    • Marked as answer by Haider Alee Wednesday, September 11, 2019 10:09 AM
    Monday, September 9, 2019 2:47 PM
  • Thanks a lot Collin for helping

    Hayder Alee

    Wednesday, September 11, 2019 10:10 AM