Dynamically changing inventory forecast

[jakeudy]

I have 3 separate tables that I am working with: Current Inventory, Open Purchase Orders, and Open Sales Orders. In the Purchase Orders and Sales Orders tables there is an Expected Delivery date field (for purchases) and expected ship date field (for sales). I have a Calendar table that I have linked both of these tables to, using their respective date fields. In all 3 tables I have an inventory site field and an item number field. Basically I want to build a line chart with time on the axis (by week), item number as the legend, and inventory level as the value, with a slicer on the side that allows me to choose which inventory site to look at. I would want the axis of my line chart to start 1 week prior to whatever the earliest date in my open purchase orders and open sales orders tables is. For example, if the earliest date in my open purchase orders was 7/6/2020 and the earliest date in open sales orders was 7/22/2020, I would want my axis to start on 6/29/2020 (one week before 7/6/2020). Then I will need to set up my calculation for inventory level. The idea is that I want to take the current inventory on hand, subtract the quantity from open sales orders, and add the quantity from open purchase orders. For example, let's say I have 30 units of item 123 currently in stock, an open sales order for 20 units for that item expected to ship on 8/4/2020, and an open purchase order for 40 units of that item scheduled to arrive 8/18. I would want my line representing inventory to stay the same up until 8/4/2020, then I would want it to dip by 20 units. Then 2 weeks later I would want to see it jump up by 40 units. So basically to calculate the projected inventory week by week, the formula would have to reference the previous week's number and add/subtract based on what open purchase/sales orders there are (if any) in that week. Is this even possible to do in Power BI? I have something similar to this set up in Excel, but I have to manually dump new data into it all the time to keep it accurate. Any help would be appreciated!

[Anonymous]

Hi @jakeudy ,

You can use measures to achieve what you need.Pls provide some sample data with expected output to make your requirement more clear.

Much appreciated.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[parry2k]

@jakeudy wow, I don't think anybody will able to understand this big blurb of text. Help us to get help.

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490