Reply
Frequent Visitor
Posts: 5
Registered: ‎11-23-2018
Accepted Solution

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!


Accepted Solutions
AlB Super Contributor
Super Contributor
Posts: 1,177
Registered: ‎11-12-2018

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


All Replies
AlB Super Contributor
Super Contributor
Posts: 1,177
Registered: ‎11-12-2018

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.

 

 

AlB Super Contributor
Super Contributor
Posts: 1,177
Registered: ‎11-12-2018

Re: Calculate future order fulfilment date

[ Edited ]

@evel83

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

AlB Super Contributor
Super Contributor
Posts: 1,177
Registered: ‎11-12-2018

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

 

 

 

Frequent Visitor
Posts: 5
Registered: ‎11-23-2018

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.

AlB Super Contributor
Super Contributor
Posts: 1,177
Registered: ‎11-12-2018

Re: Calculate future order fulfilment date

[ Edited ]

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

Frequent Visitor
Posts: 5
Registered: ‎11-23-2018

Re: Calculate future order fulfilment date

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