Forum Discussion
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 the order, etc.
ProductId Customer Order Date Stock Order
| 123 | 1 | 01-feb | 10 | 300 |
| 123 | 2 | 03-feb | 25 | 300 |
| 123 | 3 | 01-one | 35 | 300 |
| 123 | 4 | 02-may | 45 | 300 |
| 123 | 5 | 04-feb | 28 | 300 |
| 123 | 6 | Apr 21 | 39 | 300 |
| 123 | 7 | 29-may | 60 | 300 |
| 123 | 8 | 02-Jun | 90 | 300 |
| 123 | 9 | June 11-1 | 75 | 300 |
| 456 | 1 | 26-one | 10 | 200 |
| 456 | 2 | 18-one | 25 | 200 |
| 456 | 3 | June 19-19 | 20 | 200 |
| 456 | 45 | 28-feb | 20 | 200 |
| 456 | 6 | 21-mar | 75 | 200 |
| 456 | 7 | 25-Apr | 5 | 200 |
| 456 | 8 | 09-jul | 3 | 200 |
| 456 | 9 | 27-oct | 6 | 200 |
| 456 | 10 | 15-Dec | 7 | 200 |
| 456 | 11 | 20-jul | 8 | 200 |
| 78 | 5 | 18-jul | 99 | 100 |
| 799 | 8 | 01-may | 20 | 30 |
| 726 | 7 | Dec 24 | 304 | 25 |
| 7444 | 56 | 02-sep | 3 | 3 |
| 24 | 2 | 19-ago | 56 | 57 |
The drawback is that if I have 10 orders for the same product, in all 10 lines I have the value of the total stock. My goal is to be able to allocate that stock according to the order of arrival of the orders.
Best regards!
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)
13 Replies
- Migasuke
Memorable Member
Hi 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.
Attached the table sorted by date, formatted dd/mm/yy to avoid confusion
ProductId customer Order date order Stock 24 2 19/08/2021 56 57 78 5 18/07/2021 99 100 123 3 01/01/2021 35 300 123 1 01/02/2021 10 300 123 2 03/02/2021 25 300 123 5 04/02/2021 28 300 123 6 21/04/2021 39 300 123 4 02/05/2021 45 300 123 7 29/05/2021 60 300 123 8 02/06/2021 90 300 123 9 11/06/2021 75 300 456 2 18/01/2021 25 200 456 1 26/01/2021 10 200 456 45 28/02/2021 20 200 456 6 21/03/2021 75 200 456 7 25/04/2021 5 200 456 3 19/06/2021 20 200 456 8 09/07/2021 3 200 456 11 20/07/2021 8 200 456 9 27/10/2021 6 200 456 10 15/12/2021 7 200 726 7 24/12/2021 304 25 799 8 01/05/2021 20 30 7444 56 02/09/2021 3 3 - Migasuke
Hi Syndicate_Admin ,
This topic is doable but quite advanced in PQ. Unfortunatelly I will not provide you the whole PQ code, but at least I can give you some functions you will need:
1. You will need Grouping By on All rows. With combination of Index Column you will be able to do cummulative Sum on specific Product ID if needed.
2. Later you will also need some condtitional column, where you evaluate difference between Stock and Cummulative Sum
I can provide you some detailed tips if you want, but as I said, this topic is bit difficult so you might consider using DAX instead.
