cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
RobMiles Frequent Visitor
Frequent Visitor

Difference in time - work day only

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Difference in time - work day only

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

Capture.PNG

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Community Support Team
Community Support Team

Re: Difference in time - work day only

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

Capture.PNG

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.