Calculating Availability based on delivery date and stock Due date

[learner03]

I need to calculate what QTY would be avialble to sell for next 6 months starting current month to order the stock accordingly. The calculation will be based on the current available inventory, the order delivery date for the orders already in the system and the New stock that is coming in.

So the new stock take around 3 months to make, ship and receive. So if there is Stock on hand but has delivery dates for that item in coming 3 months including current month, then in the output that will show what is available to sold as current stock minus what the tola of  3months delivery is. But, in between if the new stock is landing in any of those 3 months then it will take that in too. 

I am showing some calculation below-

Input table

Item Table

Order Delivery date

New Stock Due Date

Output Required

So for example, the output calculation is as follows-

Item A= On hand 108, Sum of order delivery date in coming 3 months= 52+60 +0=112, No newstock Due in 3 months=0, then output= 108-(52+60)= (-4) for cosecutive 3 months, then in Dec 128 is landing in december 66 Qty are on delivery date and (-4) from past month, So in december availble will be (-4)+128-66=58 and then nothing in January so 58 available agagin and then in Feb 80 in coming as new stock so available=138.

Item C= On hand 117, Sum of order delivery date in coming 3 months= 113+0+0 =112, New stock Due in 3 months=100+0+0, then output= 117-113+100=104, and then nothing for delivery and nothing Stock Due, So 104 available for all the months.

Item F=  On hand 0, Sum of order delivery date in coming 3 months= 0+140+0 =140, New stock Due in 3 months=156+0+0, then output= 0-140+156=16, and then 299 for delivery in December and 426 Stock Due in December, So the remaining available from last month i.e. 16-299+426=143 available for all remaining months.

@Greg_Deckler @parry2k 

[amitchandak]

@learner03 , As per my understanding, doing the cumulative sum of both using a common date table should give you that

example

Inventory / OnHand
CALCULATE(firstnonblankvalue('Date'[Month]),sum(Init[Intial Stock in hand ]),all('Date'))

CALCULATE(SUM(Stock[New Stock]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Sales[delivery]),filter(date,date[date] <=maxx(date,date[date])))

Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw

In case you need days

https://radacad.com/calculating-stock-on-hand-using-dax-power-bi-inventory-model
https://blog.enterprisedna.co/calculate-days-of-zero-stock-power-bi-inventory-management-insights/

[learner03]

@amitchandak Thanks. I tried above  but I can't understand the output that I am getting. It doesn't considers the 3 month time frame that I mentioned in the question and I can't understand how is it calculating.  I am getting below output-