TAT of Working Business Hours and Excluding Weekend

[Technowolf]

Hi

I am trying to Calcuate TAT in business hours. I have tried couple solutions but notthing is giving me the expected result..

My Business start date is 8:30 AM and Business End date is 18:00 PM

My TAT should calcuate only Business hours and excluding Weekends.

This is what I have used.  https://community.powerbi.com/t5/Desktop/Calculating-TAT-of-working-hours/m-p/356345#M160546

Spoiler

Business TAT = DATEDIFF(
IF(
'ADX Cases'[Created Business WeekDay]>=5 && 'ADX Cases'[Created Business CET Date]>TIMEVALUE("18:00:00"),
DATEADD('ADX Cases'[Created Business CET Date].[Date],8-'ADX Cases'[Created Business WeekDay],DAY)+TIMEVALUE("08:30:00"),
'ADX Cases'[Created Business CET Date]),
'ADX Cases'[FirstTouched Business CET Date],HOUR)

The result I am getting is below.

Created Business CET Date	Created Business CET WeekDay	Case ID	FirstTouched Business CET Date	FirstTouched CET WeekDay	Business TAT	Expected Business TAT
11/29/2019 8:30	5	1	12/2/2019 12:30	1	4	13.5
11/29/2019 11:37	5	2	12/2/2019 15:18	1	7	13.75
11/29/2019 12:45	5	3	12/2/2019 16:20	1	8	13.65
11/29/2019 13:09	5	4	12/3/2019 13:08	2	29	18.97
11/29/2019 14:08	5	5	12/2/2019 9:49	1	1	5.27
12/6/2019 8:30	5	6	12/6/2019 9:35	5	-71	1.05
12/6/2019 8:30	5	7	12/9/2019 8:49	1	0	9.49
12/6/2019 8:30	5	8	12/6/2019 8:40	5	-72	0.20

I am not sure why its not working.   Can you please help me to solve this. any other solutions is welcome.

Regards,

Charles Thangaraj

[V-pazhen-msft]

@Technowolf 
I don't see any thing wrong on your dax compared with the solution in the link.
You may try:

TAT = 
var totaldiff = DATEDIFF([Created Business CET Date],[FirstTouched Business CET Date],HOUR)
var fridiff = DATEDIFF(TIMEVALUE([Created Business CET Date]),TIMEVALUE("18:00:00"),HOUR)
Return IF([FirstTouched CET WeekDay]<'ADX Cases'[Created Business CET WeekDay],totaldiff-14.5-48,totaldiff)

Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

Have you looked at: https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Duration-Working-Hours/m-p/481543#M...

[Technowolf]

Hi

I had tried this But I was getting error.  "The start date in Calendar function can not be later than the end date."

Error

I am not sure why I am getting the error

mNetWorkDuration =
// Get the start and end dates
VAR __dateStart = 'ADX Cases'[Created Business CET Date]
VAR __dateEnd = 'ADX Cases'[FirstTouched Business CET Date]
// Calculate the Net Work Days between the start and end dates
VAR __NetWorkDays = COUNTX(FILTER(ADDCOLUMNS(CALENDAR(__dateStart,__dateEnd),"WeekDay",WEEKDAY([Date],2)),[WeekDay]<6),[Date])
// Set this to the start of the work day (8:30 AM)
VAR __startTime = TIME(8,30,0)
// Set this variable to the end of the work day (6:00 PM)
VAR __endTime = TIME(18,0,0)
// Calculate the duration of a full day, in this case in minutes
VAR __fullDayMinutes = DATEDIFF(__startTime,__endTime,MINUTE)
// Calculate teh number of full days, this accounts for the possibility that tickets start and end on the same day
VAR __fullDays = IF(__NetWorkDays < 2,0,__NetWorkDays-2)
// Calculate the total duration of all full days.
VAR __fullDaysDuration = __fullDays * __fullDayMinutes
// Calculate the start time of the current record
VAR __startDayTime = TIME(HOUR(__dateStart),MINUTE(__dateStart),SECOND(__dateStart))
VAR __startDayTime1 = SWITCH(
TRUE(),
__startDayTime>__endTime,__endTime,
__startDayTime<__startTime && __startDayTime>TIME(0,0,0),__startTime,
__startDayTime
)
// Caclulate the duration of time for the first day
VAR __startDayDuration = DATEDIFF(__startDayTime1,__endTime,MINUTE)
// Calculate the end time of the current record
VAR __endDayTime = TIME(HOUR(__dateEnd),MINUTE(__dateEnd),SECOND(__dateEnd))
VAR __endDayTime1 = SWITCH(
TRUE(),
__endDayTime>__endTime,__endTime,
__startDayTime<__startTime && __startDayTime>TIME(0,0,0),__startTime,
__endDayTime
)
// Calculate the duration of time for the last day
VAR __endDayDuration = DATEDIFF(__startTime,__endDayTime1,MINUTE)
// The total duration is the duration of all full days plus the durations of time for the first and last days
RETURN
IF(__NetWorkDays=1,DATEDIFF(__dateStart,__dateEnd,MINUTE),__fullDaysDuration + __startDayDuration + __endDayDuration)

Can you please check where I am going wrong.

Thanks in advanced.

Regards,

Charles Thangaraj

[Technowolf]

Hi @Greg_Deckler 

Your solution works, But I am having a issue. I have two different date columns.

1. Create Date (its a default data, is has a calendar Icon before the column.)

2. Create Business CET Date. (This is a calculated column, it has an Icon of a column)

As per you Measure If i use Create Date your query works. but if I use Create Business CET Date it  does not work. I just get 0  has result.

Is there a way that I can format  my calculated Column to the format of my Standard Column.

My column typesboth my columns are formatted the same

Both my columns are formatted the same .

Can You guide me what to do next.

My report is complicated. 
My Source date is UTC, convert the Date to CET based on day light saving.

I have to dates

1. Created Date (Date and Time when the case was created)

2. First Touched Date (Date and Time when the case was Handled, its the first Response Time)

The working Hours is 8:30 to 18:00 and only Week days

I have Several Problems. Customer can create a Case at any point of time. So to calculate the TAT I need it to be calculated only during Working Hours and Working days.

I have a logic to convert all the cases which came in post business hours to business hours. so I have to use this calculated columns to Calculate the Networking Hours and Days. 

Your query seems to work if I use the Standard columns. can you me to fix this. 

I know this is a Measure. is it posible to convert this into calculated Columns so that I have data in row level and I can build more columns based on that data.. This would be of great help

Regards,

Charles Thangaraj

[Anonymous]

Hi

From your data you posted its looks like your dates in the code are the wrong way round

VAR __dateStart = 'ADX Cases'[Created Business CET Date]
VAR __dateEnd = 'ADX Cases'[FirstTouched Business CET Date]

Instead you want it to be

VAR __dateStart = ADX Cases'[FirstTouched Business CET Date]
VAR __dateEnd = 'ADX Cases'[Created Business CET Date]

because you firstTounched Business is before your created date, which in this format looks like you start because the date is before the created date

[Technowolf]

Hi

I tried that too, still the same error.

Create date is when the case is created and the first touched date is when the cases was handled..

Not sure why its going wrong.

Regards,

Charles Thangaraj