Stock distribution by order date

[Syndicate_Admin]

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!

[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.

[Migasuke]

Hi @Syndicate_Admin ,
can you please show me your desired output for for example ProductID 123?

[Syndicate_Admin]

Something 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]

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

[Ashish_Mathur · Accepted solution]

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)

[Syndicate_Admin]

excellent! In Power Pivot it works from 10.

Do you have an idea what modification would have to be made to make it work in Power BI?

[Ashish_Mathur]

It will work in PowerBI Desktop as well.  If my previous reply helped, please mark it as Answer.

[Syndicate_Admin]

Sisi, I marked her in response. Thanks a lot!

[Ashish_Mathur]

Hi,

If my reply helped, please mark it as Answer.

[Ashish_Mathur]

Hi,

I can solve this problem with a calculated column formula in DAX (not Power Query).  Would you be interested?

[Syndicate_Admin]

If please, I would be very good the solution by DAX

Thanks a lot!