Running balance added column

Hi Metalman1972,

Kindly follow the step-by-step approach outlined below, which may assist in resolving the issue:

1. In Power BI Service, navigate to the Dataflow section. Create a new dataflow or modify an existing one where you wish to incorporate the running balance.
2. Use the appropriate connector to establish a connection with the lakehouse dataset. Ensure that you are importing the relevant data, particularly the Date, Orders Received, and Orders Completed columns.
3. Utilise the Power Query Editor to apply transformations to the loaded data. Firstly, ensure that the columns are appropriately typed (e.g., Date, etc.).
4. Sort Data by Date:
   

   #"Sorted Rows" = Table.Sort(#"YourPreviousStep", {{"Date", Order.Ascending}})

5. Use the List.Accumulate function to compute a running balance within the dataflow:
   

   // Create a list of the running balance using List.Accumulate

   RunningBalanceList = List.Accumulate(

   #"Sorted Rows"[Orders Received],

   {0}, // Initial value for balance

   (state, current) => state & {List.Last(state) + current - #"Sorted Rows"[Orders Completed]{List.Count(state)-1}}

   )

6. Add the Resulting Column:
   

     // Convert Running Balance List into a Column

    #"Added Running Balance" = Table.FromColumns(

           Table.ToColumns(#"Sorted Rows") & {List.RemoveFirstN(RunningBalanceList, 1)},

           Table.ColumnNames(#"Sorted Rows") & {"Running Balance"}

       )

7. Group the Data:
   

   #"Grouped By Date" = Table.Group(#"Added Running Balance", {"Date"}, { 

       {"Orders Received", each List.Sum([Orders Received]), type number}, 

       {"Orders Completed", each List.Sum([Orders Completed]), type number}, 

       {"Running Balance", each List.Max([Running Balance]), type number} 

   })

8. Final Sorting:
   

   #"Sorted Final Table" = Table.Sort(#"Grouped By Date", {{"Date", Order.Ascending}})

9. Save the changes and refresh the dataflow to ensure that the running balance is computed correctly.

    

If you find this response helpful, kindly mark it as the accepted solution and provide kudos. This will aid other community members facing similar queries.

Thank you.

Hi Metalman1972,

Thank you for your kind patience.

Please find below the step-by-step solution to be implemented in the Advanced Editor:

1. Open your dataflow, then select the relevant table and click on Advanced Editor.
2. Replace or adjust your query steps ensuring that the column names exactly match your schema: "Orders Received", "Orders Completed", and "Date".
3. The solution employs List.Accumulate to simulate a row-by-row running balance. This results in the addition of a new column named "Running Balance" in your query.

If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will help other members of the community facing similar queries.

Should you have any further questions, please feel free to reach out to the Microsoft Fabric community.

Thank you.