Forum Discussion

CMoppet's avatar
CMoppet
Icon for Helper IV rankHelper IV
2 years ago
Solved

Result Not as Expected - DAX Calculating Working Hours Between Dates

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

 

Need Help

9 Replies

    • CMoppet's avatar
      CMoppet
      Icon for Helper IV rankHelper IV
      2 years ago

      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's avatar
        CMoppet
        Icon for Helper IV rankHelper IV
        2 years ago

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

    • CMoppet's avatar
      CMoppet
      Icon for Helper IV rankHelper IV
      2 years ago

      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....