Forum Discussion
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 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)
12 Replies
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-trend/ba-p/882970Hi 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,
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,
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.
