cancel
Showing results for
Did you mean:

## Stock 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!

1 ACCEPTED SOLUTION
Super User III

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)``

Regards,
Ashish Mathur
http://www.ashishmathur.com
13 REPLIES 13

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
Responsive Resident

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.

Responsive Resident

can you please show me your desired output for for example ProductID 123?

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)

Super User III

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com

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

Super User III

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)``

Regards,
Ashish Mathur
http://www.ashishmathur.com

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?

Super User III

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

Regards,
Ashish Mathur
http://www.ashishmathur.com

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

Super User III

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User III

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com

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

Thanks a lot!

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors