Inventory forecast

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:

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.