Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
evel83
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

@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
AlB
Super User
Super User

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

 

 

@evel83

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

@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

 

 

 

evel83
Frequent Visitor

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

@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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.