MRP Equation - Supply Chain Planning - Inventory Forecasting

[myassay75]

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

[Melissa]

Hi @myassay75,

Can you share a sample PBIX?
I’m sure we can help you work towards a solution. Thanks.

[myassay75]

@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)

[Melissa]

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.

Creating a dynamic Start-/EndDate for the Date table

Hi everyone, To make it easier to find here are some Power Query techniques to make the Date table Startdate and/or Enddate dynamic. If you want to give that a try, go to Power Query, select New Source, Blank Query, open the Advanced Editor, paste in this Date table M code and Invoke that function. A new query will appear in the Queries pane with the name ‘Invoked Function’ in the formula bar of that query’s Source step you can distinguish the following elements. Date table function query na…

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)

[EnterpriseDNA]

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!