topic Re: Need a DAX formula to calculate SLA hours using different working hours and public holidays in DAX Commands and Tips

Need a DAX formula to calculate SLA hours using different working hours and public holidays

Julievan — Fri, 08 Nov 2024 12:04:33 GMT

I need to calculate the business hours from a time a call is logged to the time we arrive onsite during a defined set of working hours:

Office hours	Start	End
Monday	08:00	18:00
Tuesday	08:00	18:00
Wednesday	08:00	18:00
Thursday	08:00	18:00
Friday	08:00	18:00
Saturday	08:00	17:00
Sunday / Public Holiday	08:00	16:00

Is there a formula i can use?

Re: Need a DAX formula to calculate SLA hours using different working hours and public holidays

BeaBF — Fri, 08 Nov 2024 14:38:55 GMT

Julievan Hi! Try with this measure:

BusinessHours =

VAR StartHour = TIME(9, 0, 0) -- Orario di inizio (ad esempio, 09:00)

VAR EndHour = TIME(15, 0, 0) -- Orario di fine (ad esempio, 15:00)

VAR TotalHours = 0

RETURN

SUMX(

Calls, -- La tabella che contiene gli orari di ufficio

VAR DayStart = Calls[Start] -- Orario di inizio ufficio per il giorno corrente

VAR DayEnd = Calls[End] -- Orario di fine ufficio per il giorno corrente

VAR ActualStart = IF(

StartHour < DayStart, DayStart, StartHour) -- Usa l'orario di inizio maggiore

VAR ActualEnd = IF(

EndHour > DayEnd, DayEnd, EndHour) -- Usa l'orario di fine minore

VAR HourDifference =

DATEDIFF(ActualStart, ActualEnd, MINUTE) / 60 -- Calcola la differenza in ore

RETURN

IF(HourDifference > 0, HourDifference, 0) -- Se la differenza è positiva, restituisci il valore

)

BBF

Re: Need a DAX formula to calculate SLA hours using different working hours and public holidays

lbendlin — Fri, 08 Nov 2024 14:44:01 GMT

How granular are your call and arrival times? Is minute level accuracy enough?

Please provides some sample call and arrival times, and also describe how you want to handle calls coming in outside of business hours etc. Please also provide a list of public holidays for the sample data period.

Re: Need a DAX formula to calculate SLA hours using different working hours and public holidays

Julievan — Tue, 12 Nov 2024 06:40:10 GMT

Hi there, Its very granular - please see example below

WO number (one per line)	Date - time logged	Date - Time arrived at site
WO2410250787	26-10-2024 08:00	26-10-2024 13:27
WO2410170864	17-10-2024 17:12	18-10-2024 10:00
WO2410260176	26-10-2024 14:18	27-10-2024 09:50
WO2410280062	28-10-2024 08:24	28-10-2024 11:43
WO2410110633	11-10-2024 15:19	11-10-2024 17:18
WO2410300520	30-10-2024 10:41	30-10-2024 17:16
WO2410020647	02-10-2024 13:36	02-10-2024 14:48
WO2410020793	02-10-2024 15:20	03-10-2024 09:49
WO2410110103	11-10-2024 09:31	11-10-2024 13:57
WO2410300680	30-10-2024 14:21	30-10-2024 15:07
WO2410280822	28-10-2024 15:50	29-10-2024 10:43

Here is the public holiday data:

Public Holiday

01-Jan-24

21-Mar-24

29-Mar-24

01-Apr-24

27-Apr-24

01-May-24

29-May-24

17-Jun-24

09-Aug-24

24-Sep-24

16-Dec-24

25-Dec-24

26-Dec-24

01-Jan-25

21-Mar-25

18-Apr-25

21-Apr-25

27-Apr-25

28-Apr-25

01-May-25

16-Jun-25

09-Aug-25

24-Sep-25

16-Dec-25

25-Dec-25

26-Dec-25

If calls are received outside of business hours, we only start counting from the start of the next business day. Calculation in minutes is great

Re: Need a DAX formula to calculate SLA hours using different working hours and public holidays

Julievan — Mon, 11 Nov 2024 08:40:34 GMT

Thanks for this - does it cater for public holidays (same hours as Sundays)

Re: Need a DAX formula to calculate SLA hours using different working hours and public holidays

Julievan — Tue, 12 Nov 2024 06:42:24 GMT

Here is the public holiday data:

Public Holiday

01-Jan-24

21-Mar-24

29-Mar-24

01-Apr-24

27-Apr-24

01-May-24

29-May-24

17-Jun-24

09-Aug-24

24-Sep-24

16-Dec-24

25-Dec-24

26-Dec-24

01-Jan-25

21-Mar-25

18-Apr-25

21-Apr-25

27-Apr-25

28-Apr-25

01-May-25

16-Jun-25

09-Aug-25

24-Sep-25

16-Dec-25

25-Dec-25

26-Dec-25

If calls are received outside of business hours, we only start counting from the start of the next business day. Calculation in minutes is great. We have the logged date / time and arrived date / time (format: 11-11-2024 16:36:00)

Re: Need a DAX formula to calculate SLA hours using different working hours and public holidays

Anonymous — Tue, 12 Nov 2024 08:11:37 GMT

Hi Julievan ,

Thanks to lbendlin and BeaBF for the quick replies. I have some other thoughts to add:

We can create columns.

weekday = IF([Date - time logged].[Date] in VALUES('Holiday'[Public Holiday]),7,WEEKDAY('Table'[Date - time logged]))

Column = var _date1=DATEVALUE([Date - time logged]) var _date2=DATEVALUE([Date - Time arrived at site]) var _time1= CONVERT(_date1 & " " &TIME(18,0,0),DATETIME) var _time2= CONVERT(_date1 & " " &TIME(17,0,0),DATETIME) var _time3= CONVERT(_date1 & " " &TIME(16,0,0),DATETIME) var _time4= CONVERT(_date2 & " " &TIME(8,0,0),DATETIME) RETURN SWITCH(TRUE(), _date1=_date2,DATEDIFF([Date - time logged],[Date - Time arrived at site],MINUTE), _date1 <> _date2 && [weekday]<=5 && [weekday]>=1,DATEDIFF([Date - time logged],_time1,MINUTE) +DATEDIFF(_time4,[Date - Time arrived at site],MINUTE), _date1 <> _date2 && [weekday]=6,DATEDIFF([Date - time logged],_time2,MINUTE) +DATEDIFF(_time4,[Date - Time arrived at site],MINUTE), _date1 <> _date2 && [weekday]=7,DATEDIFF([Date - time logged],_time3,MINUTE) +DATEDIFF(_time4,[Date - Time arrived at site],MINUTE))

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.

Re: Need a DAX formula to calculate SLA hours using different working hours and public holidays

Julievan — Tue, 12 Nov 2024 08:51:50 GMT

Hi Neeko,

This is exactly what I needed - thank you so much