Need Help Inventory Projection Report

[arahantha]

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

[arahantha · Accepted solution]

https://drive.google.com/file/d/1r0qHt6qF4Rd_-sQWBQ3vBq6wCvMgymAd/view?usp=share_link

[v-rongtiep-msft]

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?

How to Get Your Question Answered Quickly 

 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.

[arahantha]

Hi @v-rongtiep-msft 

First of all thank you for checking my issue

sorry for late reply, 

Please find the below Drive Link this should allow you to download sample data

https://drive.google.com/file/d/1r0qHt6qF4Rd_-sQWBQ3vBq6wCvMgymAd/view?usp=share_link

let me know if you need further details

Thanks

Arahantha