Running balance added column

[Metalman1972]

Hi all,

I am looking for help in creating an added column to a query where I have merged queries by date to show number of orders received and number of orders complete by date. The column I want to add is the to show number of orders remaining based on the previous balance + orders received - orders completed. 

Thanks in advance 

[v-pnaroju-msft · Accepted solution]

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.

[v-pnaroju-msft]

Hi Metalman1972,

We are following up to confirm whether the solution provided have resolved your issue.
If you found our response helpful, kindly mark it as the accepted solution and provide kudos, as this benefits the larger community.
If you have discovered an alternative solution to the issue, we would appreciate it if you could share it with the community to assist others facing similar challenges.

Thank you.

[v-pnaroju-msft · Accepted solution]

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.

[v-pnaroju-msft]

Hi Metalman1972,

We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.

If you found our response useful, kindly mark it as the accepted solution and provide kudos to guide other members.

Thank you.

[v-pnaroju-msft]

Hi Metalman1972,

We are following up to confirm whether the solutions provided have resolved your issue.
If you found our response helpful, kindly mark it as the accepted solution and provide kudos, as this benefits the larger community.
If you have discovered an alternative solution to the issue, we would appreciate it if you could share it with the community to assist others facing similar challenges.

Thank you.

[v-pnaroju-msft]

Hi Metalman1972,

We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.

If you found our response useful, kindly mark it as the accepted solution and provide kudos to guide other members.

Thank you.

[Metalman1972]

Hi There,

I have been working with the solutions offered and I'm running into some difficulty intergrating it into my query using the advanced editor I've been getting errors that I'm working through. 

Thanks again for the help.

[v-pnaroju-msft]

Hi Metalman1972,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank you.

[v-pnaroju-msft · Accepted solution]

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.

[v-pnaroju-msft]

Thank you, @Akash_Varuna , for your response.

Hi @Metalman1972,

We sincerely appreciate your inquiry through the Microsoft Fabric Community Forum.

Based on your query, please find attached the PBIX file along with the expected output for your reference, which may assist in resolving the issue.

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

Thank you.

[Akash_Varuna]

Hi @Metalman1972 This can be done through calculated column after loading the data please follow these 

• Sort by Date:

  • In Power BI, make sure the table is sorted by Date.

• Create a Calculated Column:

  • Go to the Modeling tab and create a New Column:

Remaining Orders = 
VAR PreviousBalance = 
    CALCULATE(
        SUM('Table'[Remaining Orders]),
        FILTER(
            'Table',
            'Table'[Date] < EARLIER('Table'[Date])
        )
    )
RETURN
PreviousBalance + 'Table'[Orders Received] - 'Table'[Orders Completed]

Replace Table with your Table name and ensure you have Orders Completed Orders Recieved column or Replace those with your actual column
If this post helped please do give a kudos and accept this as a solution
Thanks In Advance

[Metalman1972]

Hi There,

Many thanks for the help but I'm afraid this won't work for me as I cannot add a column on a direct query from a lakehouse dataset nor can I added it in the semantic model. this is why I was trying to create the column in a dataflow power query function.

Could you help with that??