Forum Discussion
Available to Promise Inventory
Hi all,
I am trying to create a table that shows the available to promise inventory. In other words, the unallocated inventory for a given week.
I have 4 key data inputs
- Current Inventory Position (On hand) - A snap shot of data taken at the start of the week which overwrites the values for each item code.
This table includes the Item Code and the Quantity. - Planned Customer Orders (Demand) - A forecast of customer orders
This table includes the Item Code, Quantity and Planned Ship Date - Receipts (On Order) - A forecast of when I am due new materials into my inventory.
This table includes the Item Code, Quantity and Planned Delivery Date - Various look-up/ helper tables for extra fields such as Item Code descriptions but I don't believe these are relevant to the problem as the item code is common across all data sources.
The issue I have is that I need to use the previous "available to promise" value as my opening inventory for the following week. I cannot share the sensitive data but an example of what I am trying to achieve is below:
| Item XYZ 1 | Week Number | (notes) | On Hand | Demand | On Order | Available to Promise (ATP) |
| 1 | This is my opening inventory > | 100 | 10 | 0 | 90 | |
| 2 | This is previous week ATP> | 90 | 40 | 10 | 60 |
The second issue is that when I generate the new on-hand sheet I need to exclude all prior dates as these will no longer be valid against the new inventory position.
This is something I could do in a few seconds in excel but my power bi knowledge is very much on a steep learning curve.
I have searched extensively but I can't quite get my head around how to solve this problem. Any support would be very welcome.
Thanks.
You are a superstar! This worked exactly how I needed it to. I see now my attempts all failed as I hadn't set the boundary for end of the week.
Thank you
3 Replies
HI GM19 ,
Try these measures.
On Hand = SUM('Current Inventory Position'[Quantity])On Order = VAR __Today = TODAY () VAR __WeekStartDate = __Today - WEEKDAY ( __Today, 1 ) + 1 VAR __WeekEndDate = __WeekStartDate + 6 RETURN CALCULATE ( SUM ( 'Receipts'[Quantity] ), ALLEXCEPT ( 'Current Inventory Position', 'Current Inventory Position'[Item Code] ), __WeekStartDate <= 'Receipts'[Planned Delivery Date], __WeekEndDate >= 'Receipts'[Planned Delivery Date] )Demand = VAR __Today = TODAY () VAR __WeekStartDate = __Today - WEEKDAY ( __Today, 1 ) + 1 VAR __WeekEndDate = __WeekStartDate + 6 RETURN CALCULATE ( SUM ( 'Planned Customer Orders'[Quantity] ), ALLEXCEPT ( 'Current Inventory Position', 'Current Inventory Position'[Item Code] ), __WeekStartDate <= 'Planned Customer Orders'[Planned Ship Date], __WeekEndDate >= 'Planned Customer Orders'[Planned Ship Date] )Available to Promise (ATP) = [On Hand]-[Demand]+[On Order]The result should be like this.
Also, attached the pbix file as the reference.
Best Regards,
Community Support Team_Gao
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
Hi Anonymous I marked this as accepted but actually upon further viewing it doesn't quite solve the problem.
I need to see the ATP by week when I add the date back in this is where my issue is:I created the relationship between the date fields which I think is what causes the issue since the opening inventory value has no date field.
Day 11 I was able to promise 100 but the following day I am only able to promise 80 despite knowing that I have 100 available. The on hand for day 12 needed to be the ATP from day 11.
I almost need to say IF the week number is this week then On hand = the sum of on hand ELSE it is the previous weeks ATP.Thanks,
You are a superstar! This worked exactly how I needed it to. I see now my attempts all failed as I hadn't set the boundary for end of the week.
Thank you
