cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

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

123101-feb10300
123203-feb25300
123301-one35300
123402-may45300
123504-feb28300
1236Apr 2139300
123729-may60300
123802-Jun90300
1239June 11-175300
456126-one10200
456218-one25200
4563June 19-1920200
4564528-feb20200
456621-mar75200
456725-Apr5200
456809-jul3200
456927-oct6200
4561015-Dec7200
4561120-jul8200
78518-jul99100
799801-may2030
7267Dec 2430425
74445602-sep33
24219-ago5657

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

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)

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

13 REPLIES 13
Syndicate_Admin
Administrator
Administrator

Nice day.

Attached the table sorted by date, formatted dd/mm/yy to avoid confusion

ProductId customer Order dateorder Stock
24219/08/20215657
78518/07/202199100
123301/01/202135300
123101/02/202110300
123203/02/202125300
123504/02/202128300
123621/04/202139300
123402/05/202145300
123729/05/202160300
123802/06/202190300
123911/06/202175300
456218/01/202125200
456126/01/202110200
4564528/02/202120200
456621/03/202175200
456725/04/20215200
456319/06/202120200
456809/07/20213200
4561120/07/20218200
456927/10/20216200
4561015/12/20217200
726724/12/202130425
799801/05/20212030
74445602/09/202133

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

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

Something like this would be the expected result:

ProductId customer Order dateorder StockExpected Result
123101-feb1030010
123203-feb2530025
123301-one3530035
123402-may4530045
123504-feb2830028
1236Apr 213930039
123729-may6030060
123802-Jun9030058
1239June 11-1753000

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)

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
https://www.linkedin.com/in/excelenthusiasts/

Nice day.

I attach the dataset sorted by date, and formatted dd/mm/yy to avoid confusion

ProductId customer Order dateorder StockExpected Result
123301/01/20213530035
123101/02/20211030010
123203/02/20212530025
123504/02/20212830028
123621/04/20213930039
123402/05/20214530045
123729/05/20216030060
123802/06/20219030058
123911/06/2021753000

Greetings and thanks

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)

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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?

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
https://www.linkedin.com/in/excelenthusiasts/

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

Hi,

If my reply helped, please mark it as Answer.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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
https://www.linkedin.com/in/excelenthusiasts/

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

Thanks a lot!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

R2 (Green) 768 x 460px.png

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 Kudoed Authors