Forum Discussion
javirmerino
Helper III
Helper IIIDate Time + x Working Hours (DAX)
First of all; thanks to everyone that's helped me out this week. I really appreciate it, guys! I'm trying to calculate an expected end time and date based on a start time and a priority status so...
v-juanli-msft
Community Support
Community SupportHi javirmerino
Is this problem sloved?
If it is sloved, could you kindly accept it as a solution to close this case?
If not, please feel free to let me know.
Best Regards
Maggie
javirmerinoHelper III
Helper IIIhi v-juanli-msft , i'm afraid its not quite resolved. The answer provided would be perfect if there was not a consideration for working hours and the time continued overnight.
However, i need to calculate a total duration based on office hours only. For example, If something Urgent is received at 16:59 on Friday they would have 4 working hours to complete. The DueDate should therefore be 12:59 on Monday not 20:59 on the same day.
i've managed to write the script in excel but am having trouble converting this into a measure due to the number of nested If formulae;
=IF([@[Required Completion Date]]=0,IF([@Urgency]="Urgent",
IF(SUM(CONVERT(HOUR([@WorkingDayRecieved]),"hr","day"),CONVERT(MINUTE([@WorkingDayRecieved]),"mn","day"))>=0.7083333,SUM(WORKDAY([@WorkingDayRecieved],1,bhols),0.541666667),
IF(SUM(CONVERT(HOUR([@WorkingDayRecieved]),"hr","day"),CONVERT(MINUTE([@WorkingDayRecieved]),"mn","day"),urgent)>=0.7083333,WORKDAY([@WorkingDayRecieved],1,bhols)+0.375+SUM(CONVERT(HOUR([@WorkingDayRecieved]),"hr","day"),CONVERT(MINUTE([@WorkingDayRecieved]),"mn","day"),urgent)-0.7083333,IF(SUM(CONVERT(HOUR([@WorkingDayRecieved]),"hr","day"),CONVERT(MINUTE([@WorkingDayRecieved]),"mn","day"))<0.375,WORKDAY([@WorkingDayRecieved],0,bhols)+0.541666667,WORKDAY([@WorkingDayRecieved],0,bhols)+SUM(CONVERT(HOUR([@WorkingDayRecieved]),"hr","day"),CONVERT(MINUTE([@WorkingDayRecieved]),"mn","day"),urgent)))),
IF([@Urgency]="Very High",
IF(SUM(CONVERT(HOUR([@WorkingDayRecieved]),"hr","day"),CONVERT(MINUTE([@WorkingDayRecieved]),"mn","day"))>=0.7083333,SUM(WORKDAY([@WorkingDayRecieved],1,bhols),0.7083333),
IF(SUM(CONVERT(HOUR([@WorkingDayRecieved]),"hr","day"),CONVERT(MINUTE([@WorkingDayRecieved]),"mn","day"),veryhigh)>0.708333333333333,WORKDAY([@WorkingDayRecieved],1,bhols)+0.375+SUM(CONVERT(HOUR([@WorkingDayRecieved]),"hr","day"),CONVERT(MINUTE([@WorkingDayRecieved]),"mn","day"),veryhigh)-0.7083333,[@WorkingDayRecieved]+veryhigh)),IF([@Urgency]="High",
IF(SUM(CONVERT(HOUR([@WorkingDayRecieved]),"hr","day"),CONVERT(MINUTE([@WorkingDayRecieved]),"mn","day"))>=0.7083333,SUM(WORKDAY([@WorkingDayRecieved],2,bhols),0.7083333),
IF(SUM(CONVERT(HOUR([@WorkingDayRecieved]),"hr","day"),CONVERT(MINUTE([@WorkingDayRecieved]),"mn","day"))<=0.375,WORKDAY([@WorkingDayRecieved],1,bhols)+0.70833333,WORKDAY([@WorkingDayRecieved],high,bhols)+SUM(CONVERT(HOUR([@WorkingDayRecieved]),"hr","day"),CONVERT(MINUTE([@WorkingDayRecieved]),"mn","day")))),
IF([@Urgency]="normal",
IF(SUM(CONVERT(HOUR([@WorkingDayRecieved]),"hr","day"),CONVERT(MINUTE([@WorkingDayRecieved]),"mn","day"))>=0.7083333,SUM(WORKDAY([@WorkingDayRecieved],3,bhols),0.7083333),
IF(SUM(CONVERT(HOUR([@WorkingDayRecieved]),"hr","day"),CONVERT(MINUTE([@WorkingDayRecieved]),"mn","day"))<0.375,WORKDAY([@WorkingDayRecieved],2,bhols)+0.70833333,WORKDAY([@WorkingDayRecieved],normal,bhols)+SUM(CONVERT(HOUR([@WorkingDayRecieved]),"hr","day"),CONVERT(MINUTE([@WorkingDayRecieved]),"mn","day")))),
IF([@Urgency]="low",
IF(SUM(CONVERT(HOUR([@WorkingDayRecieved]),"hr","day"),CONVERT(MINUTE([@WorkingDayRecieved]),"mn","day"))>=0.7083333,SUM(WORKDAY([@WorkingDayRecieved],5,bhols),0.7083333),
IF(SUM(CONVERT(HOUR([@WorkingDayRecieved]),"hr","day"),CONVERT(MINUTE([@WorkingDayRecieved]),"mn","day"))<0.375,WORKDAY([@WorkingDayRecieved],low,bhols)+0.70833333,WORKDAY([@WorkingDayRecieved],low,bhols)+SUM(CONVERT(HOUR([@WorkingDayRecieved]),"hr","day"),CONVERT(MINUTE([@WorkingDayRecieved]),"mn","day")))),""))))),[@[Required Completion Date]])