Difference in time - work day only

[RobMiles]

I have two columns that I'm comparing: an open time and close time for an issue that clients have logged.

I'm trying to produce a column that tells me how long each issue was open for. Simple right? Make a column, enter formula: close time - open time.

However our work days are 9-5:30 Mon-Fri. So if a call was opened at 4pm on a Friday and closed at 10am on a Monday I want it to tell me the call was open for 2.5 hours (as that was the amount of time it could possibly have been worked on), not 66 hours.

Can anyone help? Is there a pre-exisiting function built into PowerBI that I can use to do this?

[v-frfei-msft · Accepted solution]

Hi @RobMiles ,

One sample for your reference. Here we can create the measure as below to get the excepted result.

Measure = 
VAR cal =
    FILTER (
        CALENDAR ( MAX ( 'Table1'[log date] ), MAX ( Table1[close date] ) ),
        WEEKDAY ( [Date], 2 ) <= 5
    )
VAR logdat =
    DATE ( YEAR ( MAX ( Table1[log date] ) ), MONTH ( MAX ( Table1[log date] ) ), DAY ( MAX ( Table1[log date] ) ) )
VAR closedate =
    DATE ( YEAR ( MAX ( Table1[close date] ) ), MONTH ( MAX ( Table1[close date] ) ), DAY ( MAX ( Table1[close date] ) ) )
VAR starttime =
    TIME ( 9, 0, 0 )
VAR endtime =
    TIME ( 17, 30, 0 )
VAR day =
    COUNTROWS ( cal ) - 2
RETURN
    IF (
        day <= 0,
        DATEDIFF ( MAX ( Table1[log date] ), logdat + endtime, MINUTE )
            + DATEDIFF ( closedate + starttime, MAX ( Table1[close date] ), MINUTE ),
        IF (
            day > 0,
            day * 8.5 * 60
                + DATEDIFF ( MAX ( Table1[log date] ), logdat + endtime, MINUTE )
                + DATEDIFF ( closedate + starttime, MAX ( Table1[close date] ), MINUTE )
        )
    ) / 60