Calculating EndDateTime based on StartDateTime, Hours Of Effort Required and Working Hours

[iansheridan91]

Hi,

First time posting here, hoping someone is able to help with this.

I have 2 tables:

- Working hours (with Date, StartTime, EndTime)

Date	DayOfWeek	WorkStartTime	WorkEndTime	WorkHours
20/Oct	Wed	06:30	16:30	10
21/Oct	Thur	06:30	16:30	10
22/Oct	Fri	06:30	12:00	5.5
23/Oct	Sat	00:00	00:00	0
24/Oct	Sun	00:00	00:00	0
25/Oct	Mon	06:30	16:30	10
26/Oct	Tues	06:30	16:30	10

- Production Orders

OrderNum (Distinct)	StartDateTime	HoursOfEffort	Result
1	20/Oct/21 06:30	6	20/Oct/21 06:30
2	20/Oct/21 06:30	24	22/Oct/21 10:30

What I want to be able to do is calculate the Result column (which is the EndDateTime) based on the StartDateTime, the HoursOfEffort and working hours.

For example, if an order takes 24 hours of effort to complete it and starts on 20th Oct 2021 @ 06:30 then it calculates that the Result (EndDateTime) would be the 22nd Oct 2021 @ 10:30.

[v-jingzhang]

Hi @iansheridan91 

It is a little complicated. Will the StartDateTime in 'Production Orders' table be possible to have a time value between the WorkStartTime and WorkEndTime on a date or it will always be identical with a WorkStartTime in 'Working hours' table? 

Best Regards,
Community Support Team _ Jing

[iansheridan91]

Hi @v-jingzhang 

Yes, the StartDateTime could be any time within the 'Working hours' StartTime & EndTime.

My thought is to try break it down the 2 calculations.

Let's say i have a 3rd Production Order with a StartDateTime of 22Oct2021 at 11:00 that takes 5 hours of effort.

If i can cumulative sum the Working Hours WorkHours from the Production Order StartDate i should be able to work out the date.

So i know theres 1 hour of working hours left for 22Oct2021 (at the working day ends at12) which means i need to cover 4 hours of work. If i add that 1 hour to the next days WorkHours is there enough to cover the 4 hours, if not then add the next two days, and so on until a TRUE is returned... so 4>= 1+0 would be FALSE, then 4>= 1+0+0 would be FALSE, then 4 >= 1+0+0+10 would be TRUE, so thats my EndDate.

Then the EndTime would be the 4 hours + the Working Hours StartTime of that TRUE Date.

Think it's possible using variables but im struggling with it.

[v-jingzhang · Accepted solution]

Hi @iansheridan91 

You could use below code to add a column. I add a new column 'StartDate' to 'Production Orders' table before creating this column. 

EndDateTime = 
var vStartDate = 'Production Orders'[StartDate]
var vWorkEndDateTime = vStartDate + MAXX(FILTER('Working hours','Working hours'[Date]=vStartDate),'Working hours'[WorkEndTime])
var vRemainWorkHours = ROUND((VALUE(vWorkEndDateTime) - VALUE('Production Orders'[StartDateTime])) * 24, 2) // Round the hours to have 2 digits
var vRemainEffortHours = 'Production Orders'[HoursOfEffort] - vRemainWorkHours
var vEndDateTime =IF(vRemainWorkHours>='Production Orders'[HoursOfEffort],'Production Orders'[StartDateTime] + 'Production Orders'[HoursOfEffort]/24,
    var vTable = FILTER('Working hours','Working hours'[Date]>vStartDate)
    var vTable2 = ADDCOLUMNS(vTable,"SumOfWorkingHours",SUMX(FILTER(vTable,'Working hours'[Date]<=EARLIER('Working hours'[Date])),'Working hours'[WorkHours]))
    var vEndDate = MINX(FILTER(vTable2,[SumOfWorkingHours]>=vRemainEffortHours),'Working hours'[Date])
    return 
    vEndDate + MAXX(FILTER(vTable2,'Working hours'[Date] = vEndDate),'Working hours'[WorkEndTime]) - (MAXX(FILTER(vTable2,'Working hours'[Date] = vEndDate),[SumOfWorkingHours]) - vRemainEffortHours)/24)
return
vEndDateTime

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[iansheridan91]

Thank you so much @v-jingzhang , this works perfectly!