Inventory challenge

[Anonymous]

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 have a date table in the data model.    See image below. 

2.  Our inventory person runs an inventory report and manually creates what I am trying to do in Excel.   She uses only ONE value for the starting inventory (Qty on Hand) in the Items table.   This table contains thousands of parts, and there is a column on this table called the Last Gen Date  (essentially just the date that the information was generated  from our ERP system).    This field is mapped to my date table.  

3.   The other table (called the pegging table for some reason), contains the Outstanding Requirement (demand).    The "due date" in this table is mapped to the date table.  

Our business requriement is to take the starting inventory (from whatever the Last Gen date is) and map it against the outstanding requirement (demand).  

For example:   The Last Gen date could be Feb 9  (today's date).    For a given product, let's call it Item 12378, there could be a pre-existing demand in January of 134 units  (an order that is late and has not been manufactured yet).  

From the Items table, the Qty on Hand shows 500 units (as of today's date).    With a total February demand of 100 units.    And there could be future demand as well  (March 50 units, April 75 units, etc...).

Here is what I need to find:  

 -  Running total demand for each month  (previous months + current month)

 -  Running total of surplus for each month  (Qty on Hand - total demand).     

So, if: 

Jan Demand     Feb Qty on Hand    Feb Demand    Total Feb Demand          Total Feb Surplus            and so on......

134 units                500 units               100 units             234 units                    266  (500 - 234) 

[v-lid-msft]

Hi @Anonymous ,

Sorry for that, We cannot understand your data model clearly, Could you please provide a mockup sample based on fake data or describle the fields of each tables and the relations between tables simply? It will be helpful if you can show us the exact expected result based on the tables. Please upload your files to One Drive and share the link here.

Please don't contain any Confidential Information or Real data in your reply.

Best regards,

[Anonymous]

Hello  -  I uploaded my sample model.   Any feedback is appreciated!

Thanks!

[Anonymous]

I have modified the information in the PBIX file so that it does not show "original" information.  

Previously, I outline what I am trying to accomplish - but any questions please let me know.    

Essentially, the "Last Gen Date" is updated each time we pull information from our ERP system (Items table).    The Pegging table includes the Outstanding Requirement  (demand), and this demand is based on the Due Date column.    

I need to match up the current qty on hand (Items table), based on the Last Gen Date and deduct the demand from it.    The resulting amount is the Surplus.    If there is a demand leftover from the prior month, it needs to be rolled over to the other months  (cumulative).   

I am trying to accomplish in Power Bi what she is doing manually every day in Excel.  

PBIX file 

[amitchandak]

Can you share sample data and sample output. If possible please share a sample pbix file after removing sensitive information.Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Anonymous]

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

Any questions for me?  

[v-lid-msft]

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]

Hello - Did you see my reply?

[v-lid-msft]

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]

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]

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,

[v-lid-msft]

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,

[Anonymous]

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.