Calculating Working Hours Between Two Dates/Times

[bvbull200]

I have some data for inbound requests that I have imported as a query in Power BI.  The data includes a date and a time (separate columns) for when a request started and a date and a time (separate columns) for when the request ended.  I am trying to calculate the number of business hours between the start and finish of these requests.  I am needing this to consider that the business only runs Monday through Friday and that the relevant business hours are 8:00 a.m. - 5:00 p.m.

Here are a few scenarios that I need to accomodate.

If a request comes in on Monday, 07/01 at 10:00 a.m. and is finished on Tuesday, 07/02 at 11:00 a.m., the result should be 10:00 (as in 10 business hours).

If a request comes in on Friday, 06/28 at 4:00 p.m. and is finished on Monday, 07/01 at 9:00 a.m., the result should be 2:00 (as in 2 business hours).

If a request comes in on Monday, 07/01 at 7:00 a.m. and is finished on Monday 07/01 at 8:30 a.m., the result should be 0:30 (as in 30 business minutes or 0.5 business hours).

The data looks like this in the Power Query Editor:

There is a column called "Workflow ID" that I can use for a unique identifier, if needed.  

I have this same data in an Excel table that I have used for the past year.  The calculation that satisfied this same request is as follows (in case this helps illustrate my question):

=(NETWORKDAYS(TEXT([@[Request Creation Date]],"mm/dd/yyyy")&TEXT(" ",)&TEXT([@[Request Creation Time]],"hh:mm:ss"),TEXT([@[First Action Date]],"mm/dd/yyyy")&TEXT(" ",)&TEXT([@[First Action Time]],"hh:mm:ss"))-1)*("17:00"-"8:00")+IF(NETWORKDAYS(TEXT([@[First Action Date]],"mm/dd/yyyy")&TEXT(" ",)&TEXT([@[First Action Time]],"hh:mm:ss"),TEXT([@[First Action Date]],"mm/dd/yyyy")&TEXT(" ",)&TEXT([@[First Action Time]],"hh:mm:ss")),MEDIAN(MOD(TEXT([@[First Action Date]],"mm/dd/yyyy")&TEXT(" ",)&TEXT([@[First Action Time]],"hh:mm:ss"),1),"17:00","8:00"),"17:00")-MEDIAN(NETWORKDAYS(TEXT([@[Request Creation Date]],"mm/dd/yyyy")&TEXT(" ",)&TEXT([@[Request Creation Time]],"hh:mm:ss"),TEXT([@[Request Creation Date]],"mm/dd/yyyy")&TEXT(" ",)&TEXT([@[Request Creation Time]],"hh:mm:ss"))*MOD(TEXT([@[Request Creation Date]],"mm/dd/yyyy")&TEXT(" ",)&TEXT([@[Request Creation Time]],"hh:mm:ss"),1),"17:00","8:00")

I am still cutting my teeth with Power BI, but have very good knowledge of Excel (including VBA) and pretty good knowledge of Access, so I can comprehend some of these calculations, I just can't apply it to Power BI at the moment.

Any and all help is greatly appreciated. 

[v-frfei-msft]

Hi @bvbull200 ,

You can try to use below calculated column formula to calculate valid working hour: 
If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

Work Hour = 
VAR filtered =
    FILTER (
        ADDCOLUMNS (
            CROSSJOIN (
                CALENDAR ( [ACTIVITY_DATE], [LASTMODIFIEDDATE] ),
                SELECTCOLUMNS ( GENERATESERIES ( 9, 18 ), "Hour", [Value] )
            ),
            "Day of week", WEEKDAY ( [Date], 2 )
        ),
        [Day of week] < 6
            && [TicketID] = EARLIER ( Table1[TicketID] )
    )
VAR hourcount =
    COUNTROWS (
        FILTER (
            filtered,
            (
                [Date] >= DATEVALUE ( [ACTIVITY_DATE] )
                    && [Hour]
                        > HOUR ( [ACTIVITY_DATE] ) + 1
            )
                && (
                    [Date] <= DATEVALUE ( [LASTMODIFIEDDATE] )
                        && [Hour]
                            > HOUR ( [LASTMODIFIEDDATE] ) - 1
                )
        )
    )
VAR remained =
    DATEDIFF (
        TIMEVALUE ( [ACTIVITY_DATE] ),
        TIME ( HOUR ( [ACTIVITY_DATE] ) + 1, 0, 0 ),
        MINUTE
    )
        + DATEDIFF (
            TIME ( HOUR ( [LASTMODIFIEDDATE] ) - 1, 0, 0 ),
            TIMEVALUE ( [LASTMODIFIEDDATE] ),
            MINUTE
        )
RETURN
    IF ( hourcount <> BLANK (), (hourcount*60 + remained)/60, 0 )

 

[bvbull200]

It is doing some calculation, but it clearly isn't the correct calculation.

I am currently displaying the information on a table and looking at the Req Created Date, Req Created At, First Acted Date, and First Acted At fields for comparison.

You can see the table below (with highlights added by me):

https://1drv.ms/u/s!Akyp0hrzA6TBqi099uvUd5rLhCvY

If we look at the first highlighted row (ID # 4199979), the Req Created Date is July 3rd and the Req Created At is 3:32:29 p.m.  The First Acted Date is July 3rd and the First Acted At is 3:44:16 p.m.  The Time To First Act should be counting the time between these two instances, which is 11 minutes and 47 seconds.  The expected value would be

0.18 (as in 0.18 business hours).

For the row highlighted in purple, the expected value would also be 0.18.

These are basic examples.  There are more complex ones to figure out as well, but I want to get the easy ones done first.  An example of a more complex calculation would be row 6 (ID # 4199739) as that begins after 5 p.m. and goes through a weekend.  The expected value on that would be the equivalent of (in business time) 18 hours, 2 minutes, and 37 seconds (18:02:37) or 18.03.  This is figured because the start time for counting would be 8:00 a.m. the following day, then two full business days (9 hours each), then 2 minutes in to the next business day (Monday).

Here is the link to source data:

https://1drv.ms/x/s!Akyp0hrzA6TBqiukGz7Dvp4Kgx-8?e=EFV4dZ

Here is the link to the PowerBI project:

https://1drv.ms/u/s!Akyp0hrzA6TBqizIvVvfnZUOs5_9?e=RNirPP

I have modified the provided calculated column formula to this:

Time To First Act = 
VAR filtered =
    FILTER (
        ADDCOLUMNS (
            CROSSJOIN (
                CALENDAR ( [Req Created Date], [First Acted Date] ),
                SELECTCOLUMNS ( GENERATESERIES ( 8, 17 ), "Hour", [Value] )
            ),
            "Day of week", WEEKDAY ( [Date], 2 )
        ),
        [Day of week] < 5
            && [Workflow ID] = EARLIER ( Sheet1[Workflow ID] )
    )
VAR hourcount =
    COUNTROWS (
        FILTER (
            filtered,
            (
                [Date] >= DATEVALUE ( [Req Created Date] )
                    && [Hour]
                        > HOUR ( [Req Created Date] ) + 1
            )
                && (
                    [Date] <= DATEVALUE ( [First Acted Date] )
                        && [Hour]
                            > HOUR ( [First Acted Date] ) - 1
                )
        )
    )
VAR remained =
    DATEDIFF (
        TIMEVALUE ( [Req Created At] ),
        TIME ( HOUR ( [Req Created At] ) + 1, 0, 0 ),
        MINUTE
    )
        + DATEDIFF (
            TIME ( HOUR ( [First Acted At] ) - 1, 0, 0 ),
            TIMEVALUE ( [First Acted At] ),
            MINUTE
        )
RETURN
    IF ( hourcount <> BLANK (), (hourcount*60 + remained)/60, 0 )

Thank you for any additional suggestions. 

[bvbull200]

In case anyone finds this and attempts to solve, I have fully abandonded the idea of having Power BI do this calculation.

Power BI's inability to calculate net working days confounds me when it is such a simple formula in Excel.

As such, I have decided to do all calculations in Excel instead and import that data in to Power BI.  This has unearthed another failure of Power BI, but I will provide it's own thread for that.

Staggering how powerful PBI is on many levels, yet is inept at relatively simple things.