Solved: Re: Calculating Working hours - Page 2

rocky09 · ‎03-12-2018

I have this following Data, I am trying to find a way to calculating Working hours in betwen dates excluding Weekends.

Works hours are between: Morning 9:00 AM to Evening 6:00 PM and Saturday and Sunday are holidays.

TicketID	ACTIVITY_DATE	LASTMODIFIEDDATE
86256	28-12-2017 03:11	28-12-2017 15:11
89890	15-08-2017 20:23	15-08-2017 21:12
111611	04-10-2017 21:30	10-10-2017 13:00
111511	04-10-2017 02:30	10-10-2017 13:00
111542	04-10-2017 02:41	10-10-2017 13:00
111485	04-10-2017 02:41	10-10-2017 13:00
211411	06-10-2017 14:25	10-10-2017 13:00
150895	10-11-2017 12:32	18-01-2018 13:29
152996	08-08-2017 03:40	09-08-2017 11:00

any help?

v-shex-msft · ‎03-13-2018

HI @rocky09,

You can try to use below calculated column formula to calculate valid working hour:

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 )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Anonymous · ‎02-08-2019

I tried this solution for the data in SQL server database. I am also getting the same error:

"An argument of function 'TIME' has the wrong data type or the result is too large or too small."

Can some one please help me to replve this

KKMUrthy

rocky09 · ‎02-18-2019

Please check your Data Type for the Date Column. It should be Date format.

Anonymous · ‎02-22-2019

I ensure the data type as Datetime in query editor. I then changed the datetime format to the one shown in the screenshot

Anonymous · ‎02-26-2019

I chose the data type as Datetime for the date fields in query editor. I then changed the datetime format to the one shown in the screenshot in the data tab. I still get the same error

any thoughts please?

Paolo · ‎03-21-2019

replace the comma with a semicolon

Anonymous · ‎03-22-2019

Hi

Thank you for spending time on this issue.

I am getting the syntax error: The syntax for ';' is incorrect.

for using ; in place of ,

I am using the power BI Desktop( Aug 2018)

Any further help is greatly appreciated

rocky09 · ‎11-10-2018

Did you checked your data type of Date Column in query editor?

rocky09 · ‎03-13-2018

@v-shex-msft

Thank you so much sir.

I am getting the below error.

The Start date in Calendar function can not be later thanthe end date.

I guess, the first column may have greater date than modified date. Is there a way to dealth it

v-shex-msft · ‎03-16-2018

Hi @rocky09,

>>I guess, the first column may have greater date than modified date. Is there a way to dealth it

It means your table contains records which start date greater than end date. Datediff function not support calculated with records who have greater startdate(compare with end date).

You can add some conditions to ignore calculation when 'start date' greater than 'end date'.

Notice: DATEDIFF(start date, end date, unit)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

omarevp · ‎10-06-2018

Hi @v-shex-msft I got a question for you:

I have 2 issues when I use your solution.

1. Error because those fields in calendar can not be blank.

2. Error because sometimes the lastdate is previous to firstdate.

Can you PLEASE give me some solution? I know those are conditions I have to add in the same code you put. But I don't know where to make it work.

Thanks man! I hope you to be fine.

mpalha04 · ‎08-01-2018

Hi,

I tried the formula above and I get the error message "The start date or end date in Calendar function can not be Blank value". Any way to resolve this?

Thanks