- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# Calculate future order fulfilment date

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-10-2018 04:36 AM

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!

Solved! Go to Solution.

Accepted Solutions

## Re: Calculate future order fulfilment date

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-10-2018 09:04 AM

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

All Replies

## Re: Calculate future order fulfilment date

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-10-2018 06:04 AM

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.

## Re: Calculate future order fulfilment date

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-10-2018 08:03 AM - edited 12-10-2018 08:03 AM

## Re: Calculate future order fulfilment date

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-10-2018 09:04 AM

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

## Re: Calculate future order fulfilment date

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-10-2018 09:20 AM

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

## Re: Calculate future order fulfilment date

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-10-2018 09:24 AM - edited 12-10-2018 09:27 AM

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.

## Re: Calculate future order fulfilment date

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-10-2018 10:26 AM

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