Overview
Beginning inventory and ending inventory represent the quantities of a product at the start and end of a specific period.
Beginning inventory: Quantity of a product at the start of a given time period.
Ending inventory: Quantity of the same product at the end of that time period.
Creating balance reports by cumulative totals, you can better calculate turnover rates, inventory costs, and other performance metrics, track the stock movements, and make informed decisions.
How to Design It?
Note:
The method described here calculates beginning and ending balances only for products with stock movements during the period. Items with no inbound or outbound records will not appear in the report.
1. Select the Data Sources via Data Stream in Data Factory.
2. Aggregate data by the desired period, e.g., Year–Month.
3. Use formulas to determine the transaction amount (inbound – outbound) for each period.
4. Add cumulative fields for cumulative inbound and cumulative outbound quantities.
5. Calculate Ending Balance: Cumulative Inbound – Cumulative Outbound
6. Calculate Beginning Balance: Ending Balance + Outbound – Inbound
7. Visualize the calculation results in a Dashboard.
What Does It Look Like?
How to Configure It?
Step 1 Adding Data Stream
Go to App Management, click Data Factory > + New Data Stream.
Step 2 Selecting Input Data Source
Rename the data stream and add three Input nodes. Since Material Inventory, Material Requisition, and Return of Items forms contain all the outbound and inbound records of the items, select them as the input data source.
Step 3 Setting Data Filter
Add a Data Filter node after each Input node to filter out the data that passes the approval node in the workflow. Only these data should participate in the calculation of the inbound and outbound inventory.
Step 4 Aggregating Filtered Data
Aggregate the filtered data from the three forms into a single table. Specifically, aggregate the data related to outbound inventory into one column, and the same for inbound inventory:
Combine into a single dataset with two main columns: Inbound Inventory and Outbound Inventory. Map the Quantity Claimed as outbound inventory. Map all returns and receipts as inbound.
Remove unnecessary fields (e.g., approval comments) if not needed.
Step 5 Grouping and Summarizing
Perform Group by on aggregated data. If you need to calculate the beginning and end inventory for each month, then you can group the data on a monthly basis:
This gives you a monthly outbound or inbound inventory for each product.
Step 6 Calculating Beginning and Ending Balances
Add a Field Settings node to calculate the beginning and ending inventory.
Calculate the Period Total
Add the formula:
Period Total = Inbound Inventory - Outbound Inventory
The data preview is like:
Calculating Ending Balance
Similarly, add Running Total field and set the Data of Application of Period Total as ascended. Then you can get the ending balance. Configure it as follows:
Calculate Beginning Balance
Use the formula:
Beginning Balance = Ending Balance – Period Activity
After setting all the calculations, you can preview the data:
Step 7 Publishing Data Stream to Dashboard
Publish the accumulated and aggregated data through dashboard. Members can preview the inbound inventory and outbound inventory of the beginning and end period.
I have finished reading. 🏆
👉I can't wait to Try for myself.
👉I need more HELP in Discord Forum.