Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
arahantha
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.

 

arahantha_1-1678638688824.png

 

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

 

arahantha_2-1678638726567.png

 

 

Example 2:

Product C

Initial Stock : Available

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

Output: achieved using lastnonbalnkvalue formula

 

arahantha_3-1678638801577.png

 

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

arahantha_4-1678638821239.png

 

arahantha_5-1678638868243.pngarahantha_6-1678638877924.png

 

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

arahantha_7-1678638941923.png

 

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
3 REPLIES 3
v-rongtiep-msft
Community Support
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?

 

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.

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

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.