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

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

    Hi Anonymous ,

     

    We can modifify the Qty on Hand measure as following:

     

    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] < Min ( 'Date Table'[Date] )
                )
            ),BLANK()
    )

     


    Best regards,

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

    Hi Anonymous ,

     

    How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


    Best regards,