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
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.