Inventory forecast

[Shet25]

I need help in calculating the quantity for an inventory on item level and weekly date based on weekstart date of Sunday. Let's take for instance my qty on hnd is for the week of nov 10 and i need to calculate a 10 week forecast based on sales and purchase order. So for each item the qty on hand - sales +PO will be the balance in each week. I have the sales and PO gor the different item no. It will be a cumulative balance for eac week. The chart will show the IOH for ech week

[Anonymous · Accepted solution]

Hi @Shet25 ,

Thanks for reaching out to our community.

I can help with that! Here's a step-by-step guide on how to calculate the inventory forecast in Power BI Desktop, along with sample data and the calculation method.

Let's assume we have the following data for sales and purchase orders:

item_no	week_start	sales	purchase_order
A	2023-11-10	10	5
A	2023-11-17	15	10
A	2023-11-24	20	15
B	2023-11-10	5	10
B	2023-11-17	10	15
B	2023-11-24	15	20

Initial Quantity on Hand
Item A: 100 units
Item B: 50 units

Steps in Power BI Desktop
Load Data: Import your sales and purchase order data into Power BI Desktop.

Create a Date Table: Ensure you have a continuous date table that includes all the weeks you need for the forecast.

Create Measures:

Initial Quantity on Hand: Create a measure for the initial quantity on hand for each item.
Weekly Sales and Purchase Orders: Create measures to sum the sales and purchase orders for each week.
Cumulative Quantity on Hand: Create a measure to calculate the cumulative quantity on hand for each week.
Here is an example of how you can create these measures using DAX:

WeeklyPO = 
SUM('Inventory'[purchase_order])

WeeklySales = 
SUM('Inventory'[sales])

CumulativeQtyOnHand = 
CALCULATE(
    [InitialQtyOnHand] - [WeeklySales] + [WeeklyPO],
    FILTER(
        ALL('Date'),
        'Date'[Date] <= MAX('Date'[Date])
    )
)

 Use a line chart to visualize the cumulative quantity on hand over the days.
Set the date field as the x-axis and CumulativeQtyOnHand as the y-axis.

You can check more details from my attachment.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[GriffinTurner]

Thanks for the link, I will check over there.

Spoiler

To calculate a 10-week inventory forecast, start with the initial Qty on Hand (QOH) and adjust weekly using the formula: Balance = Previous Week’s Balance - Sales + Purchase Orders (PO). Repeat for each week, carrying over balances. Visualize results in a table or chart, with weeks on the X-axis and balances on the Y-axis. Sharing large files shouldn’t be as difficult as it often is. Between slow upload speeds, size caps, and compression requirements, most conventional methods fall short when it comes to heavy file transfers. FileMail changes the game by offering unlimited size transfers with fast and secure uploads. It’s the perfect solution for industries like media production, software development, and data science, where large files are an everyday necessity.