Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
michael_knight
Post Prodigy
Post Prodigy

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
v-xicai
Community Support
Community Support

Hi @michael_knight ,

 

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.