# MRP Equation - Supply Chain Planning - Inventory Forecasting

Hello Everyone,

I am new to the Forum and was hoping that I could get some guidance on where to look to solve a piece of the puzzle I have been working on.

I work in Supply Chain and have been asked to build a tool in Power BI that functions as an MRP.

The goal of the tool is to forecast an inventory value based on several variables over the forecast horizon. I have been working on the concept with simple numbers and would like to apply it to the real data set.

The equation is as follows:
Current Inventory - Demand + On Order = Ending Month Invetory
Ending month Inventory = Current Inventory for the next period - this has been the piece I have not been able to work out. I keep running into a circular reference issue.

Is this discussed in a video on the sight or has someone run into this problem before?

Screenshot of current numbers.

Please let me know if I can provide further clarity, and thank you in advance!

Mark Yassay

Hi @myassay75,

Can you share a sample PBIX?

@Melissa - Here you go. The Excel sheet has the test data that I was working to try and replicate the Expected result tab in Power BI.

Test Data_Share.xlsx (17.7 KB)

MPT Tool Test_Share.pbix (717.4 KB)

Hi @myassay75,

One observation, you have a massive Date table, if at all possible restrict that to the number of years you actually require. Because some calcs are now iterating over a very large Date table.
You could for example set up a dynamic start- and enddate for your date table, here’s more on that.

Next I created a couple of measures:
This calculates the last ProductPlanning[Date ] with an inventory value.

``````LastStockDate =
CALCULATE( LASTDATE( ProductPlanning[Date ] ),
FILTER( ALL( ProductPlanning ),
ProductPlanning[Current inventoery ] <>0 &&
NOT( ISBLANK( ProductPlanning[Current inventoery ] )) &&
ProductPlanning[Date ] <= MAX( viewDates[Date] )
))
``````

This returns that Inventory value

``````Inventory =
CALCULATE( SUM( ProductPlanning[Current inventoery ] ),
FILTER( ALL( viewDates ), viewDates[Date] = [LastStockDate]
))
``````

Used a cumulative pattern for Demand

``````Cum Demand =
VAR InventoryDate = [LastStockDate]
VAR ThisDate = MAX( viewDates[Date] )
RETURN

CALCULATE( SUM( ProductPlanning[Demand ] ),
DATESBETWEEN( viewDates[Date], InventoryDate, ThisDate )
)
``````

And for Replenishment

``````Cum Replenishment =
VAR InventoryDate = [LastStockDate]
VAR ThisDate = MAX( viewDates[Date] )
RETURN

CALCULATE( SUM( ProductPlanning[On Order ] ),
DATESBETWEEN( viewDates[Date], InventoryDate, ThisDate )
)
``````

Finally

``````Current Inventory level =
[Inventory] - [Cum Demand] + [Cum Replenishment]
``````

.

With this result.

This is based on your illustration of having only one initial stock position.
Here’s my sample file. I hope this is helpful.
eDNA - MPT Tool Test_Share.pbix (711.3 KB)

2 Likes

Hi @myassay75, we hope that you are having a great experience using the Support Forum so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!