Forum Discussion

GM19's avatar
GM19
New Member
4 years ago
Solved

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 Po...
Need Help
  • GM19's avatar
    GM19
    4 years ago

    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's avatar
Anonymous
Not applicable
4 years ago

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's avatar
GM19
New Member
4 years ago

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,