cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Patron
Post Patron

Hours difference between dates, excluding out of work hours

Hi,

 

I've got multiple dates in my report which show the different stages of the workflow. I want to measure how long it takes to get to each step but I don't want to include out of work hours. For example if one time is 26/08/2019 at 4pm and the next one is 27/08/2019 at 10 am I don't want the difference to be 18 hours, I want it to be 3 hours difference

 

Out work hours are 8:00am-6:00pm and I want to exclude weekends 

 

How would I go about doing this?

 

Regards,

 

Mike

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: Hours difference between dates, excluding out of work hours

Hi @michaelknight94 ,

 

You can refer to the similar case: https://community.powerbi.com/t5/Desktop/Calculating-Working-hours/td-p/374255.

 

Best Regards,

Amy

 

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

Highlighted
Post Patron
Post Patron

Re: Hours difference between dates, excluding out of work hours

Hi Amy,

 

Gave that a go. It works for one date to date but I'm unable to replicate the code and use it for another column. 

Hour Difference - Lead Created On/Created On = 
VAR filtered = 
    FILTER(
        ADDCOLUMNS(
            CROSSJOIN(
                CALENDAR( Opportunity[new_leadcreatedon], Opportunity[createdon]), 
                SELECTCOLUMNS(GENERATESERIES(9,18), "Hour", [Value] )
                ),
                "Day of the week",  WEEKDAY([Date],2) 
                ), 
                [Day of the week] <6 ) 
VAR hourcount =    
    COUNTROWS(
        FILTER(
            filtered,
            (
                [Date] >= DATEVALUE( [new_leadcreatedon])
                && [Hour] 
                > HOUR( [new_leadcreatedon]) + 1
            )
                && (
                    [Date] <= DATEVALUE(Opportunity[createdon])
                    && [Hour]
                    > HOUR(Opportunity[createdon]) - 1
                )
        )
    )
VAR remained = 
    DATEDIFF( 
        TIMEVALUE(Opportunity[new_leadcreatedon]),
        TIME(HOUR( Opportunity[new_leadcreatedon]) + 1, 0, 0), 
        MINUTE
    )
        + DATEDIFF(
            TIME(HOUR(Opportunity[createdon]) - 1, 0, 0),
            TIMEVALUE(Opportunity[createdon]),
            MINUTE
        ) 
Return
    IF(hourcount <> BLANK(), (hourcount*60 + remained)/60,0) 

I tried to use the same code but change the date fields but it's not working. Here's the code what the result is 

Hour Difference - Created On/Date Last Assigned = 
VAR filtered = 
    FILTER(
        ADDCOLUMNS(
            CROSSJOIN(
                CALENDAR( Opportunity[createdon], Opportunity[new_datelastassigned]), 
                SELECTCOLUMNS(GENERATESERIES(9,18), "Hour", [Value] )
                ),
                "Day of the week",  WEEKDAY([Date],2) 
                ), 
                [Day of the week] <6 ) 
VAR hourcount =    
    COUNTROWS(
        FILTER(
            filtered,
            (
                [Date] >= DATEVALUE( [createdon])
                && [Hour] 
                > HOUR( [createdon]) + 1
            )
                && (
                    [Date] <= DATEVALUE(Opportunity[new_datelastassigned])
                    && [Hour]
                    > HOUR(Opportunity[new_datelastassigned]) - 1
                )
        )
    )
VAR remained = 
    DATEDIFF( 
        TIMEVALUE(Opportunity[createdon]),
        TIME(HOUR( Opportunity[createdon]) + 1, 0, 0), 
        MINUTE
    )
        + DATEDIFF(
            TIME(HOUR(Opportunity[new_datelastassigned]) - 1, 0, 0),
            TIMEVALUE(Opportunity[new_datelastassigned]),
            MINUTE
        ) 
Return
    IF(hourcount <> BLANK(), (hourcount*60 + remained)/60,0) 

Row 1.PNG

 Also, do you know how I can make it so it only show the number to 2 decimal places on the column that works

Row 2.PNG

 

Thanks,

 

Mike

 

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors