Need Expert Help - Inventory

[Anonymous]

Hello, 

The forums have been great, but I still have no solution to my issue.    I've enclosed a file with sample data and I can explain what is needed  (apparently harder than it sounds).  

Our inventory team asked me to create a Power Bi report that tracks on-hand inventory, cumulative surplus (carried over from one month to the next), and the resulting starting inventory of the next month.   The "surplus" of one month, essentially becomes the starting Qty on Hand for the next month.     So in the example below, the March qty on hand should be the result of 206056 minus the total demand.   But the March qty on hand is clearly not correct.    The "total demand" cumulative formula is working fine.   

The issue is with getting the correct Qty on Hand from the total demand  (and doing so for each consecutive month).  

PBIX Sample 

[PaulDBrown]

@Anonymous 

See if this works:

(I've created new measures for the example):

(New) Current Demand = SUM('Flu_PlanPegging'[Outstanding Requirement])

Quantity on Hand = SUM(Items[Quantity On Hand])

Cumulat. Demand = CALCULATE([(New) Current Demand];
                FILTER(ALLSELECTED('Date Table');
                'Date Table'[Date] <= MAX('Date Table'[Date])))

Cumul. Quantity on Hand = 
            CALCULATE([Quantity on Hand];
                FILTER(ALLSELECTED('Date Table');
                'Date Table'[Date] <= MAX('Date Table'[Date])))

Balance quantity on hand from prev month = //Including any stock added this month
VAR calc = [Cumul. Quantity on Hand] - [Cumulat. Demand] + [(New) Current Demand]
 RETURN
 IF(ISINSCOPE('Date Table'[MonthName]);  calc; BLANK())

Please bear in mind that the cumulative calculations will be affected by the Date (year) Selection since they are using ALLSELECTED. In other words, both the Quantity on hand an total demand will start afresh at the beginning of the year selected, So adjust this to meet your business rules:

- use ALL if the stock and demand should be calculated since the beginning of time.

- if you wan the stock to be from the beginning but you want to be able to monitor the demand within the year selected the solution needs tweaking slightly..

Here is the PBIX: Stock Status 

[Anonymous]

Hi Paul, 

Very much appreciate this your efforts here...I would have never come up with this (still a newbie to some degree).  

A couple of issues/questions: 

1.  As I understand it, the starting inventory balance for each month should be subtracting the total cumulative demand.   The file seems to only subtract the current demand.    How can I modify that to have the cumulative demand subtracted? 

2.  I noticed if I throw on a date (month) slicer, that if I choose the current month things like fine (Feb).   But any other month I select then the qty on hand shows zero.   I'm sure that's not on accident, but is there a way to change that so that selecting a given month will show me the same data that the unfiltered view shows?  

Many thanks again,

texmex

[PaulDBrown]

@Anonymous 

Balance quantity on hand from prev month = 
VAR calc = [Cumul. Quantity on Hand] - [Cumulat. Demand] + [(New) Current Demand]
 RETURN
 IF(ISINSCOPE('Date Table'[MonthName]);  calc; BLANK())

The above measure basically calculates, for each month in the filter context, the cumulative sum of "Quantity on Hand", subtracts the cumulative demand (upto and including that month) and then adds the current demand for that month (and then returns the measure value only if the filter context if within Date Table [MonthName] - in other words, if the visual is filtered by the MonthName field, the measure returns the appropriate value (so for the total it remains blank).

This in effect is returning the balance from the previous month + stock added on that month. Since the cumulative demand in any month includes the demand for that month, if you subtract this value from the cumulative stock you are also subtracting the demand for that particular month - which is wrong since it not relevant to the previous month, So by adding the demand for that month to the equation, the result is the balance from the previous month + stock added on that month. Does that make sense? (try it in Excel with simple numbers,,,)

As regards your second question, the measures as they are take into account the dates based on how they are filtered (since they include the ALLSELECTED in the filter expression). This means that if you filter down to a month, the measures will calculate values ONLY for that month, which is why you are seeing 0 values: there is no demand or quantity on hand in that particular month.

That is what I was referring to in my statement about business rules:

Please bear in mind that the cumulative calculations will be affected by the Date (year) Selection since they are using ALLSELECTED. In other words, both the Quantity on hand an total demand will start afresh at the beginning of the year selected, So adjust this to meet your business rules:

- use ALL if the stock and demand should be calculated since the beginning of time.

- if you wan the stock to be from the beginning but you want to be able to monitor the demand within the year selected the solution needs tweaking slightly..

So, if the Quantity in Hand (or Stock) is ongoing since the beginning of time, you need to substitute the ALLSELECTED for ALL. This will make the measure calculate the "Quantity on Hand" for the whole date range in your model. Bear in mind that if you do so, you should also do the same with the cumulative demand measure, otherwise the result for the balance at the beginning of the month will be wrong. So, for the calculations, the cumulative measures should both include either ALLSELECTED or ALL.

If you wish to include a measure in the visual to SEE what the cumulative demand is for the dates selected in the slicer, you can create a new one which would be:

Cumulat. Demand (for the period selected) = CALCULATE([(New) Current Demand];
                FILTER(ALLSELECTED('Date Table');
                'Date Table'[Date] <= MAX('Date Table'[Date])))

You can then include this in your visual.

So for this case scenario, for the calculations you use the ALL filter expression, but for the visual you show the ALLSELECTED filter expression.

Does that make sense?

If not, please help me by defining excatly what numbers you wish to see, taking into account the possible slicers, and how you calculate stock. 

[Anonymous]

Hi Paul -  Amazing feedback and very much appreciated.  Will dig into this later this evening (central US time for me).    I'll let you know my feedback and give kudos for sure.   

[PaulDBrown]

@Anonymous 

Just for the sake of clarity, I've drawn up a visual representation of the maths involved. In doing so I realised that I was inaccurate in the concept of "previous month balance", since the measures I posted actually include new stock added in the month (which I believe is what you are after; if not, the equations below provide the answer to showing the previous month balance, excluding any stock added in the month): 

(For the sake of others who might read through this thread, I am going to correct the previous posts to reflect the correct concept being calculated)

[Anonymous]

Hi Paul, 

Ok, so bare with me as Algebra was not my strong suit!   Here is a question I posed to them, and her replies.  Plus a little more context: 

1.   She runs her inventory report once a day.   From there, she generates two reports out of ERP...the Flu Plan Pegging, and the Items.   (the sample tables in my report).    The "Last Gen Date" field from the Items table is the date the beginning inventory for the whole process is based on.    It's a singular date  (the date that the report was generated).   Whatever the qty on hand was at the time of the report is what the starting point for the inventory is.  

2.   The Pegging report provides the context for the Demand (Outstanding Requirement).    

I think we're very close, but I did not quite follow your table and which formulas I would need to change to adjust, based on the comments below.   

"The on hand qty from the Items table does not take into account any outstanding requirements.  So if the Items report was generated on Feb 2 with on hand 206,656, but there was still an outstanding requirement from Jan, your actual beginning inventory for Feb should the 203,080 you stated below."

"You will need to take the cumulative of any “past due” outstanding requirements + outstanding requirements for the current month to get end of month inventory."

"My question is really about beginning month inventory. Using my example below, the “last gen date” of the Items report was Feb 2 and the on-hand amount was 206,656.  But there was an outstanding requirement in January of 3,576. In that case, is the “last gen date” of the Items report (and specifically the on-hand qty), already taking this into account? Or, is the Feb 2 on-hand inventory really 206,656 minus 3,576 (203,080)? 

[PaulDBrown]

@Anonymous 

Bear with me since I am a little lost at the moment with the process you have described:

1) She runs the report once a day. When she does so, a new calculation date is created (defined as "Last Gen date"), correct?

Now is when I am not quite understanding the context...

You mention: Whatever the qty on hand was at the time of the report is what the starting point for the inventory is.

And then say: "The on hand qty from the Items table does not take into account any outstanding requirements.  So if the Items report was generated on Feb 2 with on hand 206,656, but there was still an outstanding requirement from Jan, your actual beginning inventory for Feb should the 203,080 you stated below."

What values does the report generated each day contain? Does it have data from the dates previous to the report creation (given that she runs the report each day)? if so which? (If the "quantity on hand" must take account any outstanding requirements (from Jan in the above example) and the quantity on hand from the Items table does not take into account any outstanding requirements, you need data from the previous month -specifically the cumulative outstanding requirements-, right?) Is this [cumulative outstanding requirements] included in the daily report and if so, which field is it?

2) Does the report include a value for "Quantity on hand" for upcoming months?

Depending on how this value is generated, & what values the report contains, we need to use either the value itself or cumualtive.  So, if the report only contains values for the current month, we would use the cumulative value; if the report has "Quantity on Hand" values for future months (to cater for product ordered and delivered in the future months), we would use the SUM of the values.

3) does she need the closing balance for each month? (as I see it, there are two "closing balances":

   a. The closing balance of physical stock:  [stock - fulfilled demand] (ie physical stock remaining once products have left wharehouse)

   b. The closing stock taking into acount demand which has not been fulfilled:  [stock - fulfilled demand - non-fulfilled demand] (so, physical stock - products ordered but haven't actually left the wharehouse.) This value is what I understand  should be used in the actual calculations for the starting point or opening balance for the following month.

4) Assuming that what we use for the calculations for the starting point or opening balance for the current month is point 2.b. above, do you need the new demand for the current month to be part of the calculation)? ie: [2.b. - current demand]

or do you need to show the calculation [2.b.]

[Anonymous]

Hi @PaulDBrown 

Using Item 20303A as our example.   

The tables below are indicative of the change that can occur from one period to another.   In this case, one report ran on the 12th, and one today, on the 20th.  

I'll try to answer your question by way of example:  

Feb                                                                                                       Mar                                                        

Qty On Hand   Prev Demd   Crnt Demd   Cumu Demd       Surp        Qty on Hand   Prev Demd   Crnt Demd   Cumu Demd     Surp

206,056               10,200          3,804            14,004           192,052         192,052             0                 33,954           47,958       144,094

My interpretation of how this should work involves a couple of assumptions: 

1.  The "previous demand" is any demand prior to the Last Gen date (in the context of the current month of the report).   That is why I show 0 previous demand in March.     Current demand is today + future demand (again, in the current month context).

2.   One question is what is the best way to calculate the very beginning Qty on Hand in the event that there is outstanding demand from the month prior to the Last Gen date.   For example, if there had been leftover January demand of 56, should the Feb Qty on Hand be 206,000  -  or would it be included in Cumulative Demand?   

3.   There is no injection of new stock factored in.   Not sure why they've not asked for that, but not something I am looking at.

4.   Previous outstanding demand is just inventory that has not shipped yet for whatever reason.  

Hopefully this helps!   And thank you again for your assistance. 

Items Table  Feb 12:

Quantity On Hand	Last Gen Date
206056	2/12/2020

Pegging Table Feb 12:

Due Date	Outstanding Requirement
2/10/2020	5400
2/10/2020	4800
2/12/2020	1182
2/12/2020	1182
2/28/2020	1440

3/2/2020	12
3/2/2020	12
3/2/2020	6
3/2/2020	6
3/2/2020	12
3/2/2020	6
3/2/2020	6
3/2/2020	6
3/4/2020	12
3/6/2020	480
3/13/2020	240
3/13/2020	72
3/13/2020	1800
3/13/2020	300
3/13/2020	990
3/13/2020	300
3/13/2020	120
3/13/2020	822
3/13/2020	480
3/16/2020	504
3/16/2020	3960
3/20/2020	3780
3/20/2020	2520
3/20/2020	1080
3/20/2020	108
3/20/2020	72
3/20/2020	480
3/23/2020	900
3/23/2020	108
3/27/2020	180
3/27/2020	1440
3/27/2020	96
3/27/2020	108
3/27/2020	48
3/27/2020	12
3/27/2020	48
3/27/2020	720
3/31/2020	6000
3/31/2020	4200
3/31/2020	1800
3/31/2020	108

Quantity On Hand	Last Gen Date
202816	2/20/2020

Pegging Table Feb 20:

Due Date	Outstanding Requirement
2/10/2020	5400
2/10/2020	1560
2/20/2020	1182
2/20/2020	582
2/20/2020	600
2/21/2020	84
2/28/2020	360
2/28/2020	48
2/28/2020	1980

[PaulDBrown]

@Anonymous 

Based on the examples, my take on calculating the Quantity on Hand taking into account the leftover demand is:

[SUM of Qty on Hand] - [Prev Demd]

[Anonymous]

Hi Paul  -  Getting down my thoughts and will reply on this tomorrow.  Thanks for sticking with me on this!!