Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

Hi,

 

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

 

I have 2 tables:

- Working hours (with Date, StartTime, EndTime)

DateDayOfWeekWorkStartTimeWorkEndTimeWorkHours
20/OctWed06:3016:3010
21/OctThur06:3016:3010
22/OctFri06:3012:005.5
23/OctSat00:0000:000
24/OctSun00:0000:000
25/OctMon06:3016:3010
26/OctTues06:3016:3010

 

- Production Orders

OrderNum (Distinct)StartDateTimeHoursOfEffortResult
120/Oct/21 06:30620/Oct/21 06:30
220/Oct/21 06:302422/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.

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    Hi Anonymous 

     

    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.

4 Replies

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous 

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        v-jingzhang
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi Anonymous 

         

        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.