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