Complex Stock Allocation Logic

[sh-smita]

Hi,

I am trying to allocate stock to sales orders using DAX, PowerBI.

But the allocation logic is not straight forward. This is how the logic must work -

1. Check if Main warehouse has stock, if stock available then move to the next sales order with Open Qty and repeat.

2. When stock in the main warehouse is exhausted/insufficient to fulfil Qty Open, check if stock is availabe in the other waehouse, if stock is allocated from other warehouse then the corresponding ETA confirmed date should be displayed for the sales order.

3. When stock in the main warehouse is exhausted/insufficient to fulfil Qty Open, chcek the stock order on POs and display the corresponding ETA unconfirmed date.

I have attached a snapshot for reference.

I have been at it for a few days and I am unable to crack the logic.

Would be grateful for any suggestions on how to implement this.

Thanks,

Smita

[Nathaniel_C]

Hi @sh-smita ,

Please attach your pbix file or copy and paste the different tables from Excel. That gives us the ability to play with the DAX and provide you a solution. 

If you would share your pbix, or dummy up some values in Excel both for current and expected data. Please copy and paste them into your post, rather than doing a picture, we may be able to help you. 

Please read this post to get your question answered more quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Nathaniel