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
    Icon for Community Support rankCommunity 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
        Icon for Community Support rankCommunity 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,