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

[Julievan]

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?

[Anonymous · Accepted solution]

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. 

[Julievan]

Hi Neeko,

This is exactly what I needed - thank you so much

[lbendlin]

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.

[Julievan]

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)

[Julievan]

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

[BeaBF]

@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

[Julievan]

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