Result Not as Expected - DAX Calculating Working Hours Between Dates

[CMoppet]

Hello

I am using DAX I found on a forum to help me calculate Response Time between two dates, only within working hours and on working days.  Below, you can see the DAX and some results:

It's not giving me the expected result, and I'm not sure why.  In the example highlighted yellow, the call was created on 03/04/23 at 14:54 and we arrived on the 04/04/23 at 10:51.  The number of working hours (08:30-17:00, Mon-Fri) between the two dates/times is circa 4.2 hours, but I'm getting a result of 2.4 hours.   All the results are lower than they should be.   Please can you help me figure out why?  I adapted this DAX from something I found in a forum, so I am perhaps missing the logic behind something.  The clock should just count hours within those working hours, stopping at 17:00 and starting again at 08:30 the following day.

DAX pasted below, if it helps...   Many Thanks!

BusHours Open = VAR BusHoursStart = CALCULATE(SELECTEDVALUE(BusinessHours[Time]),BusinessHours[Business Hours]="Start")

                 VAR BusHoursEnd = CALCULATE(SELECTEDVALUE(BusinessHours[Time]),BusinessHours[Business Hours]="End")

                 VAR BusHoursPerDay = VALUE(BusHoursEnd - BusHoursStart)*24

                 VAR ThisStartTime =  [Created Time]

                 VAR ThisEndTime = [Actual Start Time]

                 VAR StartDate = [Created Date]

                 VAR EndDate = [Actual Start Date].[Date]

                 VAR FirstDayElapsedTime = SWITCH(TRUE(),

                                            RELATED('Date'[Work Day])=0,0,

                                            ThisStartTime>=BusHoursEnd,0,

                                            ThisStartTime <= BusHoursStart,BusHoursPerDay,  

                                            StartDate = EndDate && ThisEndTime < BusHoursEnd,round((ThisEndTime-ThisStartTime)*24,3),  

                                            round((BusHoursEnd-ThisStartTime)*24,3))

                VAR LastDayElapsedTime = SWITCH(TRUE(),

                                            LOOKUPVALUE('Date'[Work Day],'Date'[Date].[Date],EndDate)=0,0,

                                            ThisEndTime<=BusHoursStart,0,  

                                            ThisEndTime >= BusHoursEnd,BusHoursPerDay,  

                                            StartDate = EndDate,0,

                                            round((ThisEndTime - BusHoursStart)*24,3))

                VAR FullWorkDays = CALCULATE(sum('Date'[Work Day]),DATESBETWEEN('Date'[Date], StartDate+1,EndDate-1))  

                VAR TotalHours = FirstDayElapsedTime + FullWorkDays*BusHoursPerDay + LastDayElapsedTime

                RETURN TotalHours

[Greg_Deckler · Accepted solution]

@CMoppet I did this once: Net Work Duration (Working Hours) - Microsoft Fabric Community

[CMoppet]

As an add on, when I did it, I got errors because some of my rows have no start date or time.  And it didn;t seem to like that.  For the rows without any dates, I just want blank results....

[CMoppet]

Hello 🙂  I've already tried your method today!  It didn't like me!  All my results were 510...for every row.  I couldn't figure out why!   Anything obvious wrong with the different approach I used above?

[CMoppet]

Hello again!  Just one more thing....I really want to make your method work, and add in something that recognises if a date is in my Holidays list, as we shouldn't count internal response time on those dates.  Is this possible, please?  Thank you

[CMoppet]

Just tried it again....not sure what I'm missing...

[Greg_Deckler]

@CMoppet So, did you get it to work? It's marked as solved so I just wanted to follow up and see if you were able to get it to work.

[CMoppet]

Hello 🙂  Unfortunately not.  I get the same result for every row of data.  I presume I'm not adapting it properly for my data, but I can't see where the issue is.  I marked it as 'Solved' as I am trying a different approach.  I'd still be so grateful if you can see where I'm going wrong with your solution, as the alternative I've found runs really slowly...

[Greg_Deckler]

@CMoppet OK, post a sample of your data as text or a link to the PBIX file if possible.

[CMoppet]

Hello 🙂   I'm unable to attach a file or link due to security.  I have opened your pbix file attached the the NetWorkDuration post you linked, so I could try and figure out the issue, but I'm finding the same problem there too....it's giving the same results for each row.  What am I doing wrong??!! , and yet I'm getting the same result for them all!