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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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)


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


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


Thank you for reading and solve this case



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


let me know if you need further details






Helpful resources

Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.