Forum Discussion
Syndicate_Admin
Administrator
AdministratorStock distribution by order date
Hello! I have a problem that I can't solve with Power Query. In the work there is an SAP report in which the orders of all the customers are, with quantities ordered, reference stock, date of th...
Hi,
This calculated column formula works
=MAX(if(Data[Stock]>CALCULATE(SUM(Data[order]),FILTER(Data,Data[Order date]<=EARLIER(Data[Order date])&&Data[ProductId]=EARLIER(Data[ProductId]))),Data[order],Data[Stock]-CALCULATE(SUM(Data[order]),FILTER(Data,Data[Order date]<EARLIER(Data[Order date])&&Data[ProductId]=EARLIER(Data[ProductId])))),0)
Migasuke
Memorable Member
Memorable MemberHi Syndicate_Admin ,
can you please show me your desired output for for example ProductID 123?
Syndicate_AdminSomething like this would be the expected result:
ProductId customer Order date order Stock Expected Result 123 1 01-feb 10 300 10 123 2 03-feb 25 300 25 123 3 01-one 35 300 35 123 4 02-may 45 300 45 123 5 04-feb 28 300 28 123 6 Apr 21 39 300 39 123 7 29-may 60 300 60 123 8 02-Jun 90 300 58 123 9 June 11-1 75 300 0 It allocates stock to each customer until it covers 300 units. For the 8th order I have only 58 remaining units, but the order is 90, so I assign the 58 available and to the last order I do not assign anything (Since the 300 were assigned to previous orders)
- Ashish_Mathur
Super User
How does one interpret 01-one, 26-one, June 11-1? Also, shouldn't the Order date be sorted in ascending order by ProductID? Please share a revised clean dataset.
- Syndicate_Admin
Nice day.
I attach the dataset sorted by date, and formatted dd/mm/yy to avoid confusion
ProductId customer Order date order Stock Expected Result 123 3 01/01/2021 35 300 35 123 1 01/02/2021 10 300 10 123 2 03/02/2021 25 300 25 123 5 04/02/2021 28 300 28 123 6 21/04/2021 39 300 39 123 4 02/05/2021 45 300 45 123 7 29/05/2021 60 300 60 123 8 02/06/2021 90 300 58 123 9 11/06/2021 75 300 0 Greetings and thanks