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

Calculating Working hours

Hi, 

 

i want to calculate the working hours that passed between the arrive of an email and the final answer to the client.

I found a very good script at this link https://community.powerbi.com/t5/Desktop/Calculating-Working-hours/m-p/374828#M170248 but there is a problem into it that i can't solve. 

 

The formula starts to count the hours from the time in which the email arrived but it applies the same time for starting the count also on subsequent days; the fact is for the days in the middle i want the formula to take into consideration all the working hours (8:30 - 19:30). For example, if an email arrived at 12:25 of 29/03/19 the formula will count from that time (and it's fine), but it will do the same also for 30/03/19 (i want to change this).

Basically i want to add another line that manages the count for the days in the middle. 

 

I was thinking to something like:

 

VAR hourcount =

COUNTROWS (

FILTER (

filtered,

( [Date] > DATEVALUE ( [ACTIVITY_DATE] )

&& [Hour] > HOUR ( [ACTIVITY_DATE] ) + 1 )

 

&& ( [Date] <= DATEVALUE ( [LASTMODIFIEDDATE] )

&& [Hour] > HOUR ( [LASTMODIFIEDDATE] ) - 1 ) ) )

 

&&( [Date] > DATEVALUE ( [ACTIVITY_DATE] +1 )

&& [Hour] > HOUR ( 9:00 ) )

 

Could someone help me?Cattura.PNGStarting Date (left) & End Date (right)

The formula:

 

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 )

 

4 REPLIES 4
Community Support Team
Community Support Team

Re: Calculating Working hours

Hi @Roma28 ,

 

Could you please 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.

 

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.
Roma28 Frequent Visitor
Frequent Visitor

Re: Calculating Working hours

Hi Frank @v-frfei-msft 

 

thanks for the help !! I have sensible data in the original file, so i created a new one with just index, start & end date

 

 

https://luissmy.sharepoint.com/:u:/g/personal/luca_romano_studenti_luiss_it/EeACcVnEIxNHiuCL6fmcpc8B... --> powerBi file

 

https://luissmy.sharepoint.com/:x:/g/personal/luca_romano_studenti_luiss_it/EdF8jYf2S8hIltb8ROG0vVkB... --> excel table

Community Support Team
Community Support Team

Re: Calculating Working hours

Hi @Roma28 ,

 

I cannot access the link as you shared. Could you please share the files again by another way?

 

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.
Roma28 Frequent Visitor
Frequent Visitor

Re: Calculating Working hours

@v-frfei-msft 

 

https://drive.google.com/drive/folders/1Ks7rL5ROAV73SD1mRd-NbWrwfXbdI9vP?usp=sharing

 

Try now ! I created a folder on Google Drive

 

Tell me if you have more trouble to access it