cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
evel83 Frequent Visitor
Frequent Visitor

Calculate future order fulfilment date

Hi all,

 

First post here. I need some help with calculating an estimated 'production completed' date in the future. Based on past daily production I can estimate how many days of production are needed to fulfil the order.

For example: 10 units can be produced each day. 30 units must be produced to fulfil the order. Starting from december 10th, the estimated date on which the order will be fulfilled will be 12 december (3 days).

 

I want to take into account that production stops during weekend. This is my challenge. I've chosen to connect via directquery to the source database, so I don't think I can create a date table. The solutions I can find all require a date table.

 

My strategy was to use the the calendar function starting from today, filter out all weekends and after this filter out the first x number of rows where x is the number of production days. The last date in the table should be the date on which the order will be fulfilled. I can't make this work in DAX. Perhaps there is some easier or better solution. Can someone help me with this calculation or get me on the right track?

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Calculate future order fulfilment date

@evel83

An option would be to calculate the "weekend crossings" that you have and then add the corresponding days (2 days for each "crossing").  If you have a column with the weekday number (1-7 for Mon-Sun) for the date production starts you could create a calculated column WeekendCrossings
 

Table1[WeekendCrossings]= 
QUOTIENT ( ( Table1[ProductionStartWeekDay] - 1 ) + ( Table1[NumProductionDays] - 1 );5 )

and then another column:

Table1[DayOffset] = Table1[NumProductionDays] - 1 + (2 * Table1[WeekendCrossings])

 

If you add Table1[DayOffset] to the date of initial production you'd have the date of production end. This might need some tweaking but it gives you the general idea. Note that I use [NumProductionDays]-1 because if the duration is one day you'll finish that very day (according to your explanation above) and thus offset=0

 

 

 

View solution in original post

6 REPLIES 6
Super User
Super User

Re: Calculate future order fulfilment date

@evel83

 

This is more difficult if you cannot have a 'Date' table. Taking this into account, I do not think the logic you've followed is far from a good solution. 

Can you show the code you say you can't get to work? Then we could help. If you can share the pbix even better.

 

 

Super User
Super User

Re: Calculate future order fulfilment date

@evel83

Is the production start date always outside the weekend? I guess so

Super User
Super User

Re: Calculate future order fulfilment date

@evel83

An option would be to calculate the "weekend crossings" that you have and then add the corresponding days (2 days for each "crossing").  If you have a column with the weekday number (1-7 for Mon-Sun) for the date production starts you could create a calculated column WeekendCrossings
 

Table1[WeekendCrossings]= 
QUOTIENT ( ( Table1[ProductionStartWeekDay] - 1 ) + ( Table1[NumProductionDays] - 1 );5 )

and then another column:

Table1[DayOffset] = Table1[NumProductionDays] - 1 + (2 * Table1[WeekendCrossings])

 

If you add Table1[DayOffset] to the date of initial production you'd have the date of production end. This might need some tweaking but it gives you the general idea. Note that I use [NumProductionDays]-1 because if the duration is one day you'll finish that very day (according to your explanation above) and thus offset=0

 

 

 

View solution in original post

evel83 Frequent Visitor
Frequent Visitor

Re: Calculate future order fulfilment date

@AlB: Thanks for your reply! This might work. I'm going to struggle with the computation. I will let you know if I succeed.

Super User
Super User

Re: Calculate future order fulfilment date

@evel83

Sure, let me know. I haven't tested it extensively but it seems to work IF all production start dates fall outside weekend. I have not considered Sat or Sun as start day.

evel83 Frequent Visitor
Frequent Visitor

Re: Calculate future order fulfilment date

It works for me, thanks! Start date can't be Sat or Sun, so that's fine. 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)