cancel
Showing results for
Did you mean:
Frequent Visitor

## Need Help Inventory Projection Report

Hi Everyone,

I've been working on this problem for a few months now
Here I am trying to achieve a projected Inventory Report. So far I have 5 scenarios where I could solve the partial problem, Need expert helps to get the complete solution.

Below data model is a sample where actual data has 60K+ product lines and a few thousand  transaction records for each product(MRP IN &OUT)

Formula

Projected Stock:

Current Month= InitialStock + (Total IN <=Current Month)- (Total OUT <= Current Month)

Future Months = Previous Month end Stock + Given Month Total IN -Given Month Total OUT

Data Sample attached

https://www.dropbox.com/s/s0hqk9pbcoeh7uu/ProjectedStockTest.pbix?dl=0

Data available

Product Table:  has Products A,B,C,D,&E with Unit price

Initial Stock Table: has Initial stock details where Quantity On hand >0

MRP Table: has information on Stock IN & OUT by the due date for few months (not all parts have IN & OUT transaction records)

Desired Result: End of month Projected Stock by Quantity and Value \$ for all months.

Example 1: Ideal case

Product A

Initial Stock: Available

Stock IN&OUT: Transaction record available

Output: meets expectation projected stock by quantity visible for all months

Example 2:

Product C

Initial Stock : Available

Stock IN & OUT: transaction  does not exists for few months (202303 missing)

Output: achieved using lastnonbalnkvalue formula

Example 3

Product B

Initial Stock: Not available (product name doesn't exists on the table since there is no stock on Hand)

Stock IN & OUT: record exists

Out Put: Desired result Achieved

Example 4 : Issue need to be solved

Product E

Initial Stock: Available

Stock IN & OUT : no record exists

Output: desired Out Put is to show whatever available stock in all months

since there is no transaction record exist method used to solve above all issues not captured this issue

Example 5;

Product D; Similar to product C

I have attached the Power Bi data sample

Happy to share more examples/Details if needed

you can scrap the complete model and create a new solution that can work efficiently so that I can extend this report to the next level

https://www.dropbox.com/s/s0hqk9pbcoeh7uu/ProjectedStockTest.pbix?dl=0

Thank you for reading and solve this case

Regards

Arahantha

1 ACCEPTED SOLUTION
Frequent Visitor
3 REPLIES 3
Frequent Visitor
Community Support

Hi @arahantha ,

I'm very sorry I'm late, I can't download your pbix file anymore, have you solved the problem? Can you provide more detailed information?

please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

Frequent Visitor

First of all thank you for checking my issue

let me know if you need further details

Thanks

Arahantha

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors