cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dr_Chris
Helper I
Helper I

Too much for PowerBI?

Happy New Year! 🎉

I'm always looking for a challenge so when our Operations Director asked if they could manage their component stock forecasting through PowerBI I thought I'd create a beautiful report that answered every question... but now I'm wondering if this is too much for PowerBI and we ought to generate the underlying data in using a program running in C or Python and then just use PowerBI to visualise the results.

 

At the moment production planning is done using a huge and complex spreadsheet


So here's the problem:

  • We manufacture a number of electronic products.
  • These are built from components, some of which are on long lead times so we need to be pretty good at workng out when the stock will run out
  • Each product has a Bill of Materials (BOM) which tells us how many of each component are needed to build the finished product
  • We also have a production plan - detailing how many of each product we are planning to build each week

The Operations Dirctor would like two reports:

  1. A weekly view of how many of each component will be used (This report is used to plan warehouse spare / stock movements ready for production)
  2. A weekly view of the remaining component stock levels - with any low stock areas highlighted:

Something like this:

image_2021-01-01_084648.png 

 

 

 

 

image_2021-01-01_084736.png

 

 

 

 

 


In terms of input data I have:

  • A lst of products
  • The component inventory
  • The assembly items / BOM, an
  • The production plan

Here are some examples of the input data using invented data/products:

image_2021-01-01_084932.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

To tackle this in Python I'd start by generating a table for each product to calculate the weekly component usage for each product based on the production plan

These can then be added together to get the total component count for each week and then we just need to work though each week working out the stock level at the end of the week based on the opening inventory and the cumulative component usage.

 

Of course the next step is to look at what components have been ordered, with predicted / historic leade times to add these into the future inventory levels.  I'll tackle that once I've got the basics sorted

 

 

Any help / advice / guidance greatly appreciated!

Thanks

Chris

 

 

 

1 ACCEPTED SOLUTION
MFelix
Super User III
Super User III

Hi @Dr_Chris ,

 

I assume you have a model wiht one to many relationships similar to the one below:

 

MFelix_0-1609505206283.png

 

Be aware that the production table need to be unpivot to look like this:

 

MFelix_1-1609505307939.png

 

Add the following measure to your report:

 

Production = SUM('Production Plan'[Value])

Required = SUM('Assembly details / BOM'[Qty Required])

Total Needed = SUMX(Products, [Production] * [Required])

Inventory =
CALCULATE (
    SUM ( 'Component Inventory'[Qty in Stock] ),
    CROSSFILTER ( 'Component Inventory'[Item Code], 'Assembly details / BOM'[Item Code], BOTH )
)
    - CALCULATE (
        [Total Needed],
        FILTER (
            ALL ( 'Production Plan' ),
            'Production Plan'[Week] <= SELECTEDVALUE ( 'Production Plan'[Week] )
        )
    )

 

Now just use the last measure in your visualization:

 

MFelix_2-1609505349048.png

 

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

6 REPLIES 6
StefanoGrimaldi
Solution Sage
Solution Sage

for this you would need to go into inventory theory itself, select a inventory policy to follow, maximun stock policiy, economic order quiantity policy etc, and them configure each parameter on the Power BI example: 

Economic order quantity: 

Measure or column 1 (average demand in the last year or forescasted demand depending on the inveotry model)= Average montly demand of the component. 

Measure or column 2 (inventory reorder point) = (Measure 1 * leap time ) + Safetly inventory

Measure or column 3 (saftly inventory) = Measure 1 * (Saftly inventory in % policy example (20%)) * (1 + possible dealy time in order delivery)

Measure or column 4 (amount  to order) = measure 1 * (inventory stock policy) example: measure 1 * 1.5 month of inventory. 

note: all time its a 0 to 1 propocition of the time used 1 month =1 ; 1 month and a half 1.5 ando so on, there more to see and take in consideration etc, but for that would reccomend go to the bases itself and see what row you need or are taking for this. 

Dr_Chris
Helper I
Helper I

@MFelix  I've taken your suggestion and now built this into our live SQL Database - and it's working brilliantly
Now there are a number of bells and whistles that we would like to add:  


1. We know the lead time for each item.   How can I use this to highlight when items need to be reordered to avoid running out of stock?   

Here's the extended Component Inventory table with the Lead Time (Wks) added

 

image_2021-01-03_105615.png

 

Ideally we would include this information on the table already generated;

image_2021-01-03_105707.png

 

 

Perhaps by highling the cell in the week when the order is required.  Alternatively we just need another table - perhaps listing each week and the items that need to be ordered in that week

 

image_2021-01-03_105758.png

 

2. The next request I get will be to include any items that are on order in the calculations.   
I have access to the order date, lead time, quantity and expected arrival date - so the simplest approach would be work out the week when the items arrive and then add that into the Inventory figures.   

Thanks - Really appreciate the support !

 

StefanoGrimaldi
Solution Sage
Solution Sage

hey, 

in general no, isnt too much for power BI to handle, actually we just did something like this for material consuption forecast and inventory needs vs demand for operations and totally managable and fair easy, just work one little component at the time of the forescasting formula and others try each value carefuly before heading to the next step and you will have it build it up in no time. 

MFelix
Super User III
Super User III

Hi @Dr_Chris ,

 

I assume you have a model wiht one to many relationships similar to the one below:

 

MFelix_0-1609505206283.png

 

Be aware that the production table need to be unpivot to look like this:

 

MFelix_1-1609505307939.png

 

Add the following measure to your report:

 

Production = SUM('Production Plan'[Value])

Required = SUM('Assembly details / BOM'[Qty Required])

Total Needed = SUMX(Products, [Production] * [Required])

Inventory =
CALCULATE (
    SUM ( 'Component Inventory'[Qty in Stock] ),
    CROSSFILTER ( 'Component Inventory'[Item Code], 'Assembly details / BOM'[Item Code], BOTH )
)
    - CALCULATE (
        [Total Needed],
        FILTER (
            ALL ( 'Production Plan' ),
            'Production Plan'[Week] <= SELECTEDVALUE ( 'Production Plan'[Week] )
        )
    )

 

Now just use the last measure in your visualization:

 

MFelix_2-1609505349048.png

 

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

This project is continuing to grow.
I've now managed to highlight cells where a reorder is needed in that week to prevent the stock running out based on the current build plan.

Dr_Chris_0-1611783736395.png

image_2021-01-27_214234.png

This item is on a long lead time and I start to see warnings in week 25 and a red alert in week 27.
I'd now like to add in the stock that is on order.  This is stored in a table "PurchaseOrderDetails" - the important columns are:

Dr_Chris_1-1611784209922.png


I'm struggling to create the right measure(s) to add the ordered items back into the Inventory value so that the increase is included in the right columns in the table above.

Thanks again for your help in cracking this problem

 

Thanks
This worked perfectly!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.