Modifying Turnaround Time to be more Dynamic

[Anonymous]

Hi. I have a question related to the calculation (Dax formulas) for SLA metrics on the processing time for Purchase Orders.

This is one of them:

Time_Turnaround = NETWORKDAYS([Created],[CompletionDate/Time],1,{dt"2023-11-23",dt"2023-11-24",dt"2023-12-25",dt"2023-12-26",dt"2024-1-1"})

Where

[Created Date] = the date/time when the PO request is received and a record is created and logged into the system, and

[Completion Date] = the date/time when the PO request has been approved

As you see, the Dax code will only count “days” which are business days, and exclude holidays.

Questions:

1. Is there a way to make the code more dynamic, i.e., instead of hard coding the holidays, e.g., “2023-12-25” is Christmas day, etc. to write the code instead to automatically exclude holidays every year
2. Can we modify the code so as to build in business hours, e.g., any request that comes in after 5pm on a specific date will be considered as received the next business day?

Thank you!

[Anonymous]

Thank you @Fowmy The DAX correction works. However, the 2 records that were created on 12/5 after 5pm, which means they should have been considered as received on 12/6, so turnaround time (completed on 12/8) should be 3 days, but they are showing 4 days. Can you tweak the turnaround time DAX to resolve that? Thanks so much!

[Fowmy · Accepted solution]

@Anonymous 

In our example the 1st one is 5 AM so 4 days is correct, the 2nd one should be 3. I modifed to include 5 as well using >=, please check

Time_Turnaround = 

VAR __Time = HOUR([Created])
VAR __Diff = NETWORKDAYS([Created],[CompletionDate/Time],1,Distinct(HolidaysTable[Holiday]))
VAR __Result = IF( __Time >= 17 , __Diff - 1 , __Diff )
RETURN
    __Result

[Anonymous]

@Fowmy You are a GENIUS! Yes that worked! Thank you a bajillion!

[Fowmy]

@Anonymous 
With regards to your question # 1: you need to create a separate table to maintain the holidays list or you can use your dates or calendar table to add one more column for holidays. This field could be used in place of the hard-coded holidays in your formula.

Example: Time_Turnaround = NETWORKDAYS([Created],[CompletionDate/Time],1,HolidaysTable[Holiday])

question # 2: Please explain your second question and provide some sample data with the expected output.

[Anonymous]

Hello, thanks for #1. As for #2, pls see inserted screenshot. The Created dats is 12/5/2023 at 5:44pm, which means the request came in after business hours (which end at 5:00pm), and it was completed on 12/8/2023. So the turnaround time is calculated as 4 days when it should have been 3 days because if it was received on 12/5 at 5:44pm, it should be considered as received/created on 12/6. So that is what I mean. How can I code the Created Date so that if it is received after 5pm on that date, then the Created Date should be pushed to the next day. Thank you. 

[Fowmy]

@Anonymous 

I modifed the formula, please check.

Time_Turnaround = 

VAR __Time = HOUR([Created])
VAR __Diff = NETWORKDAYS([Created],[CompletionDate/Time],1,HolidaysTable[Holiday])
VAR __Result = IF( __Time > 17 , __Diff - 1 , __Diff )
RETURN
    __Result

[Anonymous]

Hello @Fowmy , thanks again so much for helping me. 

I created the Holiday table (see picture), the holiday date field is therefore 'Holidays'[Date].

However, there is a DAX error (see picture).

Would you please help me correct this error?

Again, thanks a billion!
Aisha

[Fowmy]

@Anonymous 
Please use the following code:

Time_Turnaround = 

VAR __Time = HOUR([Created])
VAR __Diff = NETWORKDAYS([Created],[CompletionDate/Time],1,Distinct(HolidaysTable[Holiday]))
VAR __Result = IF( __Time > 17 , __Diff - 1 , __Diff )
RETURN
    __Result