Available to Promise Inventory

[GM19]

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

1. 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. 
   
   
2. Planned Customer Orders (Demand) - A forecast of customer orders
   This table includes the Item Code, Quantity and Planned Ship Date
   
   
3. 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
   
   
4. 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.

[GM19 · Accepted solution]

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

[Anonymous]

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!

[GM19]

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,