Calculating value but factoring in working hours and days

[PatrickWork2021]

looking for help!

I have an "open" table with a created date and time field that shows when a case was opened, I also have a "CreatedDate" field which is just a date field. I also have a calendar table with a field called "date" which I use to model to each other. My calendar table also has "Fin Days of Week No" (Sunday is 0, Monday is 1 and so on), my calendar table also has "Fin Day of Week Name" with Sunday, Monday etc in.

I would like to calculate the number of hours a case has been open but also factor in working hours and days. 

I used CoPilot to get me so far using the below created columns. My results are not correct.

For example. (Consider the time to now be 11am on Friday). Date is DD/MM/YYYY. Working hours 8-5

I have a record with 11/10/2024 7:54:00 that shows a value of 3 which is correct as we have had 3 working hours.

I have a record with 11/10/2024 10:24:00 that shows a value of 10 which is obviously not factoring in working hours.

I have a record with 09/10/2024 14:29:00 that shows a value of 24. I think this should be about 15.5 hours. (2.5 on the 9th, 8 on the 10th and then 3 so far on the 11th)

IsWeekday = IF('Calendar'[Fin Days of Week No] > 0 && 'Calendar'[Fin Days of Week No] < 6, 1, 0)

WorkingHoursDifference = 
VAR CreatedDateTime = 'Open'[CreatedDate - Copy]
VAR CreatedDateOnly = DATE(YEAR(CreatedDateTime), MONTH(CreatedDateTime), DAY(CreatedDateTime))
VAR CurrentDateTime = NOW()
VAR CurrentDateOnly = DATE(YEAR(CurrentDateTime), MONTH(CurrentDateTime), DAY(CurrentDateTime))
VAR DateRange = FILTER(
    'Calendar',
    'Calendar'[Date] >= CreatedDateOnly && 'Calendar'[Date] <= CurrentDateOnly && 'Calendar'[IsWeekday] = 1
)
VAR TotalDays = COUNTROWS(DateRange) - 1
VAR StartDayHours = 
    IF(
        HOUR(CreatedDateTime) < 8, 
        0, 
        IF(HOUR(CreatedDateTime) >= 17, 
           0, 
           17 - HOUR(CreatedDateTime)
        )
    )
VAR EndDayHours = 
    IF(
        HOUR(CurrentDateTime) < 8, 
        0, 
        IF(HOUR(CurrentDateTime) >= 17, 
           9, 
           HOUR(CurrentDateTime) - 8
        )
    )
VAR WorkingHours = 
    IF(
        TotalDays < 0, 
        EndDayHours - StartDayHours, 
        (TotalDays * 9) + StartDayHours + EndDayHours
    )
RETURN
WorkingHours

I don't know what else to try

Help appreciated.

[Anonymous]

Hi, ALL,
Firstly  Greg_Deckler thank you for you solution!
And @PatrickWork2021 ,I tried to change your code, in the current sample data, is able to fulfill your needs, but my data is very small, may not be able to reproduce all your problem, this problem is logically complex, I hope my answer can solve your problem!

WorkingHoursDifference = 
VAR CreatedDateTime = MAX('Table'[CreatedDateTime]) 
VAR CreatedDateOnly = DATE(YEAR(CreatedDateTime), MONTH(CreatedDateTime), DAY(CreatedDateTime)) 
VAR CurrentDateTime = NOW() 
VAR CurrentDateOnly = DATE(YEAR(CurrentDateTime), MONTH(CurrentDateTime), DAY(CurrentDateTime)) 


VAR DateRange = 
    FILTER(
        'Date',
         'Date'[Date] >= CreatedDateOnly &&  'Date'[Date] <= CurrentDateOnly &&  'Date'[IsWeekday] = 1
    )


VAR EndDayHours =
    IF(
        HOUR(CurrentDateTime) <= 8, 
        0,
        IF(HOUR(CurrentDateTime) >= 17, 
            9, 
            HOUR(CurrentDateTime) - 8 
        )
    )


VAR TotalDays =
 IF(EndDayHours=0,
     COUNTROWS(DateRange)-2,
    COUNTROWS(DateRange) - 1) 


VAR StartDayHours =
    IF(
        HOUR(CreatedDateTime) < 8 && TotalDays = COUNTROWS(DateRange) - 2, 
        17 - HOUR(CreatedDateTime) - 1, 
        IF(HOUR(CreatedDateTime) >= 17, 
        0, -- No hours counted
        17 - HOUR(CreatedDateTime) 
        )
    )


VAR WorkingHours =
    IF(
        TotalDays = 0, 
        StartDayHours, 
        TotalDays * 9 + StartDayHours + EndDayHours 
    )

RETURN
WorkingHours

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@PatrickWork2021 I did this once before generative AI existed:

Net Work Duration (Working Hours) - Microsoft Fabric Community

[PatrickWork2021]

Hi Greg, I can try this one out for sure.

In terms of the start and end dates reference here, what are those dates? The data I am looking at currently only has a created date time as the tickets are still open so I am looking to show this as of "now". Would your measure only work for a record that has a created and closed date? If so, that functionality is already built into our Salesforce CRM for me to calculate the business hours of closed tickets, as mentioned its the live ones I want to see the true age of.

VAR __dateStart = MAX([Date_Start])
VAR __dateEnd = MAX([Date_End])

[Anonymous]

Hi @PatrickWork2021 ,

It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered? If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

[PatrickWork2021]

Hi, I replied to the solution, I dont feel it was what I was after as it was more about completed tickets not live ones.