Date Time + x Working Hours (DAX)

[javirmerino]

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 i can determine if the request was completed within SLA. Weekends and holidays need to be excluded from this calculation, as well as hours outside of the operational 9-5. For example, an urgent incident received at 16:59 would be due at 12:59 the next working day (+4 working hours), while an urgent incident received any time after that but before 9am would be due by 13:00..

An added level of complexity would be that the expected hours to complete each incident varies depending on the priority level;

Urgency	Hours
Low	40
Medium	24
High	16
Very High	8
Urgent	4

Fortunately i have NextWorkingDay, IsWeekDay and IsHolidayUK as fields within my date dimension table, as well as a number of pre-calculated fields such as the correct StartingPoint.

ID	Received	ReceivedDate	ReceivedTime	NextWorkingDay	StartDate	StartTime	StartingPoint	Urgency
72937	06/01/2020 11:07	06/01/2020	11:07:29	07/01/2020	06/01/2020	11:07:30	06/01/2020 11:07	Urgent
73021	07/01/2020 11:13	07/01/2020	11:13:35	08/01/2020	07/01/2020	11:13:36	07/01/2020 11:13	Urgent
73303	10/01/2020 16:24	10/01/2020	16:24:23	13/01/2020	10/01/2020	16:24:23	10/01/2020 16:24	Normal
73307	10/01/2020 16:53	10/01/2020	16:53:45	13/01/2020	10/01/2020	16:53:46	10/01/2020 16:53	Very High
74065	28/01/2020 08:08	28/01/2020	08:08:51	29/01/2020	29/01/2020	09:00:00	29/01/2020 09:00	Low
74179	29/01/2020 15:31	29/01/2020	15:31:35	30/01/2020	29/01/2020	15:31:36	29/01/2020 15:31	Normal
74191	29/01/2020 16:40	29/01/2020	16:40:50	30/01/2020	29/01/2020	16:40:50	29/01/2020 16:40	Low
74287	31/01/2020 13:03	31/01/2020	13:03:49	03/02/2020	31/01/2020	13:03:49	31/01/2020 13:03	Urgent
74419	04/02/2020 16:31	04/02/2020	16:31:50	05/02/2020	04/02/2020	16:31:50	04/02/2020 16:31	High
74436	05/02/2020 09:39	05/02/2020	09:39:30	06/02/2020	05/02/2020	09:39:31	05/02/2020 09:39	Normal

However, i have absolutely no idea where to start with the calculation so was wondering if anyone had written something similar, previously? I've found some similar answers from several years back but these were all relative to Power Query/M Language as opposed to DAX - so i'm wondering if its even possible?!

[PatrickT]

@javirmerino Did you find a solution? i am searching for this aswell.

[v-juanli-msft]

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

[javirmerino]

hi @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]])

[vanessafvg]

@javirmerino   you could something like this, just need to adjust for your names

expected end time =
IF (
    isworkday = 1
        && isholiday = 0,
    issues[StartingPoint] + TIME ( RELATED ( lookup[Hours] ), 0, 0 ),
    BLANK ()
)

[javirmerino]

Thanks @vanessafvg, thats about as far as i got, too. This would be my solution if it were just adding hours, but i need it to add working hours only.

Somehow, the hours added timer needs to stop at 5pm and restart on 9am the next working day. The issue being that Low priority requests have a 40 hour SLA so they wouldnt even be due in the same week, let alone the same day!