Available to Promise Inventory overview per Week

[Anonymous]

Hello community!

I am trying to get my around to following: I am required to provide my organization with a weekly overview of stock availabilities., which is bascially an extension of another thread i found on this forum: Click 

In Excel, this is a two-step approach.

• Firstly I am deducing the expected inventory per week (based on stock on hand and expected movements)
• Secondly, I work towards the net availabilities per week based on
  
  1. The projected inventory in the weeks until the end of the given period (if there is an expected future shortage, I can't promise anything)
  2. The projected inventory for a given week (if this is lower than or equal to zero, I can't promise anything)
  3. The ending balance for the period (if I have a positive week balance and no expected future shortages, but the current weekly balance exceeds the ending balance, I shouldn't promise more than the ending balance)

 In Excel this would look like this. 

In PowerBI I managed to solve for the expected inventory per week (measure 'Projected Inventory'). In addition, I created a Help Measure which is, in a not so smart way, checking for each balance in the consecutive weeks 

VAR Stock =
CALCULATE (
            MAX ( Stock[Item] ),
            FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ))    

VAR Week1
CALCULATE (
        SUM ( Verkoop[Aantal] ),
        FILTER ( ALL ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) + 7)

RETURN
SWITCH(TRUE(),
Stock + Week1 < 0, "Yes",
etc.,"No")

Using the below, I am fulfilling the conditions set out by 1 through 3 above.

Net availability = 
SWITCH(TRUE(),
[Help Measure]="Yes",0,
[Projected Inventory] <= 0, 0,
[Projected Inventory]<[Ending Balance],[Projected Inventory],
[Ending Balance])

I am wondering how I can make this more efficient and for that I am requesting your help. I think that my help measure in particular can be programmed a lot better (resulting in the outcome highlighted below in excel).

Looking forward to hearing from you! 

[Anonymous]

PBIX file 

[amitchandak]

@Anonymous ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

@amitchandak 

https://drive.google.com/file/d/19A6EFPtDiSwjkc_MieBzPR5SqCoAjG4-/view?usp=sharing

Please note that the data included differs from the excel screencap above. The idea is the same though