Calculating time difference between two date and time columns

[JohnLow]

Hi,

I'm currently trying to create a dax formula to calculate the time difference between a date/time column named 'Created at' in the tickets table and a date/time column named 'Last Closed At' in the tickets table. I'm only including dates where the value is Yes for 'IsWorkingDay' in the Date Table, 'DateTable' . I'm also excluding the time between 18:00 and 08:00. My result should be in hours.

TimeDifference = 
VAR StartDateTime = MIN('Tickets'[Created at])
VAR EndDateTime = MAX('Tickets'[Last closed At])
VAR StartDate = DATE(YEAR(StartDateTime), MONTH(StartDateTime), DAY(StartDateTime))
VAR EndDate = DATE(YEAR(EndDateTime), MONTH(EndDateTime), DAY(EndDateTime))
VAR WorkingDays =
    COUNTROWS(
        FILTER(
            'DateTable',
            'DateTable'[IsWorkingDay] = "Yes"
                && 'DateTable'[Date] >= StartDate
                && 'DateTable'[Date] <= EndDate
        )
    )
VAR StartHour = HOUR(StartDateTime)
VAR EndHour = HOUR(EndDateTime)
VAR StartMinute = MINUTE(StartDateTime)
VAR EndMinute = MINUTE(EndDateTime)
VAR TotalHours =
    IF(
        StartDate = EndDate,
        IF(
            StartHour >= 8 && StartHour < 18 && EndHour >= 8 && EndHour < 18,
            (EndHour - StartHour) + (EndMinute - StartMinute) / 60,
            IF(
                StartHour >= 8 && StartHour < 18,
                (18 - StartHour) + (0 - StartMinute) / 60,
                IF(
                    EndHour >= 8 && EndHour < 18,
                    (EndHour - 😎 + (EndMinute - 0) / 60,
                    0
                )
            )
        ),
        IF(
            StartHour >= 8,
            (18 - StartHour) + (0 - StartMinute) / 60,
            0
        ) + IF(
            EndHour < 18,
            (EndHour - 😎 + (EndMinute - 0) / 60,
            0
        )
    )
RETURN
    WorkingDays - 1 + TotalHours / 24

Formula seems to work ok for the entries on the same day but for different days as below, the results are not correct.

Any help much appreciated.

[amitchandak · Accepted solution]

@JohnLow , Refer if blog from matt can help

https://exceleratorbi.com.au/calculating-business-hours-using-dax/