Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Need Expert Help - Inventory

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 

Need Help

10 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        PaulDBrown
        Community Champion
        6 years ago

        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.