Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Inventory challenge

I've posted about this but so far have been unsuccessful in getting a solution.   Any help is very appreciated.  So, will try again to give a full explanation of what I need help with:    1.  I hav...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Amit - I shared my sample file and output  (although the output is not correct).   

 

Any questions for me?  

v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

Hi Anonymous ,

 

Sorry for our late reply, We can try to use the following measures to meet your requirement:

 

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

 

Total Demand = CALCULATE(SUM('Flu_PlanPegging'[Outstanding Requirement]), FILTER(ALLSELECTED('Flu_PlanPegging'),'Flu_PlanPegging'[Item] in FILTERS('Items Bridge'[Item]) && 'Flu_PlanPegging'[Due Date] <= MAX('Date Table'[Date])))

 

Qty on Hand or  Surplus = 
VAR CurrentQty =
    CALCULATE (
        SUM ( 'Items Bridge'[Quantity On Hand] ),
        FILTER (
            ALLSELECTED ( 'Items Bridge' ),
            'Items Bridge'[Item] IN FILTERS ( 'Items Bridge'[Item] )
        )
    )
RETURN
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( 'Date Table'[MonthOfYear] )
            < MONTH ( MIN ( 'Items Bridge'[Last Gen Date] ) ), CurrentQty
            + CALCULATE (
                SUM ( 'Flu_PlanPegging'[Outstanding Requirement] ),
                FILTER (
                    ALLSELECTED ( 'Flu_PlanPegging' ),
                    'Flu_PlanPegging'[Item] IN FILTERS ( 'Items Bridge'[Item] )
                        && 'Flu_PlanPegging'[Due Date] < MIN ( 'Items Bridge'[Last Gen Date] )
                        && 'Flu_PlanPegging'[Due Date] >= MIN ( 'Date Table'[Date] )
                )
            ),
        SELECTEDVALUE ( 'Date Table'[MonthOfYear] )
            = MONTH ( MIN ( 'Items Bridge'[Last Gen Date] ) ), CurrentQty,
        SELECTEDVALUE ( 'Date Table'[MonthOfYear] )
            > MONTH ( MIN ( 'Items Bridge'[Last Gen Date] ) ), CurrentQty
            - CALCULATE (
                SUM ( 'Flu_PlanPegging'[Outstanding Requirement] ),
                FILTER (
                    ALLSELECTED ( 'Flu_PlanPegging' ),
                    'Flu_PlanPegging'[Item] IN FILTERS ( 'Items Bridge'[Item] )
                        && 'Flu_PlanPegging'[Due Date] > MIN ( 'Items Bridge'[Last Gen Date] )
                        && 'Flu_PlanPegging'[Due Date] <= MAX ( 'Date Table'[Date] )
                )
            )
    )

 


If it doesn't meet your requirement, Could you please show the exact expected result of the 20303A  based on the Tables that you have shared?

 


Best regards,

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hello -  Thank you for this great effort.  

     

    Just a couple of questions/comments: 

     

    1.  Just curious why (or if I should) connect the Items Bridge to the date table.  I've tried but it won't let me so perhaps I would need to use the "use relationship" function.     

     

    2.   Using Item 20303A as an example, there should be no starting inventory in January  (the Last Gen Date is Feb 2).    The first starting inventory would be as Feb 2.  

     

    3.   The March starting quantity on hand should be Feb qty on hand  -  Feb total demand.   So, 206656 - 16662 which would be 189,994.     Your March qty on hand shows 182,656.   

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Hello - Did you see my reply?
    • v-lid-msft's avatar
      v-lid-msft
      Community Support
      6 years ago

      Hi Anonymous ,

       

      Sorry for delay in response, We can change our measures to following to meet your requirement:

       

      Qty on Hand or  Surplus = 
VAR CurrentQty =
    CALCULATE (
        SUM ( 'Items Bridge'[Quantity On Hand] ),
        FILTER (
            ALLSELECTED ( 'Items Bridge' ),
            'Items Bridge'[Item] IN FILTERS ( 'Items Bridge'[Item] )
        )
    )
RETURN
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( 'Date Table'[MonthOfYear] )
            = MONTH ( MIN ( 'Items Bridge'[Last Gen Date] ) ), CurrentQty,
        SELECTEDVALUE ( 'Date Table'[MonthOfYear] )
            > MONTH ( MIN ( 'Items Bridge'[Last Gen Date] ) ), CurrentQty
            - CALCULATE (
                SUM ( 'Flu_PlanPegging'[Outstanding Requirement] ),
                FILTER (
                    ALLSELECTED ( 'Flu_PlanPegging' ),
                    'Flu_PlanPegging'[Item] IN FILTERS ( 'Items Bridge'[Item] )
                        && 'Flu_PlanPegging'[Due Date] > MIN ( 'Items Bridge'[Last Gen Date] )
                        && 'Flu_PlanPegging'[Due Date] <= MAX ( 'Date Table'[Date] )
                )
            ),BLANK()
    )

       

      Total Demand = 
CALCULATE (
    SUM ( 'Flu_PlanPegging'[Outstanding Requirement] ),
    FILTER (
        ALLSELECTED ( 'Flu_PlanPegging' ),
        'Flu_PlanPegging'[Item] IN FILTERS ( 'Items Bridge'[Item] )
            && 'Flu_PlanPegging'[Due Date] <= MAX ( 'Date Table'[Date] )
    )
)

       


      Best regards,

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Unfortuantely it is still not correct. 

         

        If the Feb qty on hand   =  206,656

         

        And the total cumulative demand going into March is 16,662, 

         

        then the starting qty on hand for March should be 

         

        189,994