Forecast upcoming delivery dates to find busy periods

[henrycqc]

Question:

Is there a better way to estimate future delivery dates based on their order frequencies?

Background

Our IT system only records when we receive orders.  Store orders can be daily, weekly, monthly and quarterly.

I am trying to create a report to continuously forecast the next 3 to 6 months of order dates based on the current “Most recent delivery date” field. We currently do this monthly within an excel sheet to get an estimate of upcoming deliveries. This should help us look ahead in time to spot peek weeks when a lot of orders are coming in.

My solution

The solution below works for monthly and quarterly delivery dates but would require a lot of new columns to replicate it for daily and weekly order dates.

1. Created a reference table only hold the products the we receive monthly
• Table field (
• ID,
• product,
• Most recent delivery date)

1. I used “Date.AddMonths” function in power query to add new Columns that estimate the next 3 months delivery dates

• Table fields (
• ID,
• product,
• Most recent delivery date,
• Most recent delivery date + 1 month,
• Most recent delivery date + 2 months,
• Most recent delivery date + 3 months)

1. I then unpivoted on ID and product columns

[KT_Bsmart2gethe · Accepted solution]

Hi @henrycqc ,

I do apologise for not articulating the solution clearly.

Let's go back to your original question:

---

@henrycqc wrote:

Question:

Is there a better way to estimate future delivery dates based on their order frequencies?

---

If I understand your challenge correctly, you want to know what date has peak orders. The formula provided does the count of orders based on the frequency and the most recent delivery date for the next three months. If you apply that formula, then you will have the forecast orders for the next three months.

Also, I added a column to get the date in 3 months based on the most recent delivery date:

Date.AddMonths(Date.AddDays([#" Most recent delivery date"],-1),3)

I applied GroupBy then sorted by the date.

See below screenshot. Let me know if this is the desired outcome? 

You can achieve the above outcome either in PQ or PP. 

Regards

KT

[KT_Bsmart2gethe]

Hi @henrycqc ,

You can try to Max / MaxN function to find the TopN orders to get the date and order. Then, you can use the Duration function to find out the time length of the order placed until the next one to allow future prediction.

I am happy to assist your further with a set of dummy data.

Regards

KT

[henrycqc]

Hi

Our SQL database hold fields product name, product order frequency, and “most recent delivery date”, so I don’t believe that I need to use Max / MaxN and duration functions (see table A).

Table A

Product	Order Frequency	Most recent delivery date
Product A	daily	25/07/2022
Product B	Weekly	20/07/2022
Product C	monthly	05/07/2022
Product D	quarterly	16/05/2022

I am finding it hard to come up with an effective solution to project most recent delivery dates in the future and take account of the order frequency field.  

Example: If the date is 25/07/22 and I project the date from table A forward by three months, I should end up with following number of dates per product and frequency (see table B).

Table B (upcoming orders)

Order	Frequency	Count of new rows added to account for the next 3 months of upcoming orders, exluding weekends.
Product A	daily	65
Product B	Weekly	13
Product C	monthly	3
Product D	quarterly	1

The dates from table B would then be used power BI visuals.

hope this helps.

[KT_Bsmart2gethe]

Hi @henrycqc,

Add a custom column:

= if [#" Order Frequency"]="quarterly"
then 1
else
if [#" Order Frequency"]="monthly"
then 3
else
if [#" Order Frequency"]="weekly"
then Number.Abs(Number.Round(Duration.TotalDays([#" Most recent delivery date"] - Date.AddMonths([#" Most recent delivery date"],3))/7,0))
else List.Count(List.Select(List.Transform({Number.From(Date.AddDays([#" Most recent delivery date"],1))..Number.From(Date.AddDays(Date.AddMonths([#" Most recent delivery date"],3),-1))}, each Date.DayOfWeekName(_)), each _<>"Saturday" and _<>"Sunday")))

Regards

KT

[henrycqc]

Hi @KT_Bsmart2gethe

I ‘am sorry,

I am new to the community and I have failed to clearly communicate my issue and believe that I have wasted your time.

I was trying to forecast the dates from table A to achieve table C using either power query or DAX.

Table B was meant as a summary for table C

Thanks for all your help.

Table C

Product	Order Frequency	Most recent delivery date
Product A	daily	24/10/2022
Product A	daily	21/10/2022
Product A	daily	20/10/2022
Product A	daily	19/10/2022
Product B	Weekly	19/10/2022
Product A	daily	18/10/2022
Product A	daily	17/10/2022
Product A	daily	14/10/2022
Product A	daily	13/10/2022
Product A	daily	12/10/2022
Product B	Weekly	12/10/2022
Product A	daily	11/10/2022
Product A	daily	10/10/2022
Product A	daily	07/10/2022
Product A	daily	06/10/2022
Product A	daily	05/10/2022
Product B	Weekly	05/10/2022
Product C	monthly	05/10/2022
Product A	daily	04/10/2022
Product A	daily	03/10/2022
Product A	daily	30/09/2022
Product A	daily	29/09/2022
Product A	daily	28/09/2022
Product B	Weekly	28/09/2022
Product A	daily	27/09/2022
Product A	daily	26/09/2022
Product A	daily	23/09/2022
Product A	daily	22/09/2022
Product A	daily	21/09/2022
Product B	Weekly	21/09/2022
Product A	daily	20/09/2022
Product A	daily	19/09/2022
Product A	daily	16/09/2022
Product A	daily	15/09/2022
Product A	daily	14/09/2022
Product B	Weekly	14/09/2022
Product A	daily	13/09/2022
Product A	daily	12/09/2022
Product A	daily	09/09/2022
Product A	daily	08/09/2022
Product A	daily	07/09/2022
Product B	Weekly	07/09/2022
Product A	daily	06/09/2022
Product A	daily	05/09/2022
Product C	monthly	05/09/2022
Product A	daily	02/09/2022
Product A	daily	01/09/2022
Product A	daily	31/08/2022
Product B	Weekly	31/08/2022
Product A	daily	30/08/2022
Product A	daily	29/08/2022
Product A	daily	26/08/2022
Product A	daily	25/08/2022
Product A	daily	24/08/2022
Product B	Weekly	24/08/2022
Product A	daily	23/08/2022
Product A	daily	22/08/2022
Product A	daily	19/08/2022
Product A	daily	18/08/2022
Product A	daily	17/08/2022
Product B	Weekly	17/08/2022
Product A	daily	16/08/2022
Product D	quarterly	16/08/2022
Product A	daily	15/08/2022
Product A	daily	12/08/2022
Product A	daily	11/08/2022
Product A	daily	10/08/2022
Product B	Weekly	10/08/2022
Product A	daily	09/08/2022
Product A	daily	08/08/2022
Product A	daily	05/08/2022
Product C	monthly	05/08/2022
Product A	daily	04/08/2022
Product A	daily	03/08/2022
Product B	Weekly	03/08/2022
Product A	daily	02/08/2022
Product A	daily	01/08/2022
Product A	daily	29/07/2022
Product A	daily	28/07/2022
Product A	daily	27/07/2022
Product B	Weekly	27/07/2022
Product A	daily	26/07/2022
Product A	daily	25/07/2022