topic Re: Calculate Working Hours excl Weekends & Holidays in DAX Commands and Tips

Calculate Working Hours excl Weekends & Holidays

Anonymous — Fri, 24 Jun 2022 06:25:14 GMT

Hi All,

I have the following travelling data for vehicles where I need to calculate actual travel hours excluding weekends and holidays.

Considering Standard workings hours from 8:00 AM to 6:00 PM.

Request to help in this..

First Start Time	Last Stop Time
01-Apr-2022 07:41:26 AM	31-May-2022 04:41:07 PM
01-Apr-2022 09:03:47 AM	31-May-2022 06:29:15 PM
01-Apr-2022 06:09:36 AM	31-May-2022 05:55:29 PM
04-Apr-2022 02:41:09 PM	31-May-2022 07:00:30 PM
01-Apr-2022 08:08:56 AM	31-May-2022 06:28:53 PM
07-Apr-2022 08:59:19 AM	31-May-2022 05:10:01 PM
01-Apr-2022 12:01:36 PM	31-May-2022 07:09:00 PM
01-Apr-2022 07:34:28 AM	31-May-2022 06:31:49 PM

Re: Calculate Working Hours excl Weekends & Holidays

tackytechtom — Fri, 24 Jun 2022 07:05:44 GMT

Hi AnonymousAnonymous ,

Here a similar thread. You can find various other links in there that I am sure will be helping you!

Working days between two dates - simply! - Microsoft Power BI Community

And here a video with a function in Power Query:

PowerBI: Calculate business/working days between two dates in a new column - YouTube

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

Re: Calculate Working Hours excl Weekends & Holidays

tamerj1 — Fri, 24 Jun 2022 07:20:05 GMT

Anonymous
Where is the data for weekends and holidays?

Re: Calculate Working Hours excl Weekends & Holidays

tamerj1 — Fri, 24 Jun 2022 08:52:16 GMT

Anonymous

Here is a sample file with the solution

As the holidays data is not available the following code consideres only weekends (Saturdays and Sundays). Also late start and early stop are also considred. All calculations are curried out over minutes level then converted to HH:MM format

Working Hours HH:MM = VAR Result = SUMX ( WorkingHours, VAR CurrentStartDate = WorkingHours[First Start Time] VAR CurrentStopDate = WorkingHours[Last Stop Time] VAR StandardStartDate = DATE ( YEAR ( CurrentStartDate ), MONTH ( CurrentStartDate ), DAY ( CurrentStartDate ) ) + TIME ( 8, 0, 0 ) VAR StandardEndDate = DATE ( YEAR ( CurrentStopDate ), MONTH ( CurrentStopDate ), DAY ( CurrentStopDate ) ) + TIME ( 18, 0, 0 ) VAR LateStartMinutes = IF ( CurrentStartDate > StandardStartDate, DATEDIFF ( StandardStartDate, CurrentStartDate, MINUTE ), 0 ) VAR EarlyStopMinutes = IF ( CurrentStopDate < StandardEndDate, DATEDIFF ( CurrentStopDate, StandardEndDate, MINUTE ), 0 ) VAR AllDays = CALENDAR ( CurrentStartDate, CurrentStopDate ) VAR AllWorkingDays = FILTER ( AllDays, NOT ( WEEKNUM ( [Date], 2 ) IN { 6, 7 } ) ) VAR WorkedMinutes = COUNTROWS ( AllWorkingDays ) * 600 VAR NetWorkedMinutes = WorkedMinutes - LateStartMinutes - EarlyStopMinutes RETURN NetWorkedMinutes ) RETURN FORMAT ( QUOTIENT ( Result, 60 ), "#,#" ) & " Hours & " & FORMAT ( MOD ( Result, 60 ), "00" ) & " Minutes"

Re: Calculate Working Hours excl Weekends & Holidays

Anonymous — Fri, 24 Jun 2022 10:00:15 GMT

hii

Weekends are on Saturday & Sunday

And holidays from

02/05/2022 Monday to 06/05/2022 Firday

Re: Calculate Working Hours excl Weekends & Holidays

tamerj1 — Fri, 24 Jun 2022 10:11:59 GMT

Anonymous
Here is the updated sample file https://we.tl/t-IJwSL1qGc5

Working Hours = VAR Result = SUMX ( WorkingHours, VAR CurrentStartDate = WorkingHours[First Start Time] VAR CurrentStopDate = WorkingHours[Last Stop Time] VAR StandardStartDate = DATE ( YEAR ( CurrentStartDate ), MONTH ( CurrentStartDate ), DAY ( CurrentStartDate ) ) + TIME ( 8, 0, 0 ) VAR StandardEndDate = DATE ( YEAR ( CurrentStopDate ), MONTH ( CurrentStopDate ), DAY ( CurrentStopDate ) ) + TIME ( 18, 0, 0 ) VAR LateStartMinutes = IF ( CurrentStartDate > StandardStartDate, DATEDIFF ( StandardStartDate, CurrentStartDate, MINUTE ), 0 ) VAR EarlyStopMinutes = IF ( CurrentStopDate < StandardEndDate, DATEDIFF ( CurrentStopDate, StandardEndDate, MINUTE ), 0 ) VAR AllDays = CALENDAR ( CurrentStartDate, CurrentStopDate ) VAR AllWorkingDays = FILTER ( AllDays, NOT ( WEEKNUM ( [Date], 2 ) IN { 6, 7 } ) && NOT ( [Date] IN VALUES ( Holidays[Date] ) ) ) VAR WorkedMinutes = COUNTROWS ( AllWorkingDays ) * 600 VAR NetWorkedMinutes = WorkedMinutes - LateStartMinutes - EarlyStopMinutes RETURN NetWorkedMinutes ) RETURN FORMAT ( QUOTIENT ( Result, 60 ), "#,#" ) & " Hours & " & FORMAT ( MOD ( Result, 60 ), "00" ) & " Minutes"

Re: Calculate Working Hours excl Weekends & Holidays

Anonymous — Fri, 24 Jun 2022 20:57:29 GMT

Thank you the video was a great help, im trying to create a column with the data from the querey now to work out how many working day each person had had off in total.

work days = CALCULATE(SUM('DaysOff (2)'[Days off work]),ALLEXCEPT('DaysOff','DaysOff (2)'[UniqueID]))+0

On the datediF column that works out the calendar days off, the formula above adds up all of the days into one cell for me. I want both to be in a table the days off from a specific absence then the total days the person has taken off in total too in the same row.

eg.

B Boo = 3 12

B Boo = 6 12

B Boo = 3 12

Re: Calculate Working Hours excl Weekends & Holidays

Anonymous — Fri, 24 Jun 2022 21:03:19 GMT

Probaly not the correct way to resolve this,excuse me as im new to PBI.

I made a help column out of the querey column using value to chnage the number to text, then used my formula and it worked. 🙂

work days = CALCULATE(SUM('DaysOff (2)'[Days off work]),ALLEXCEPT('DaysOff','DaysOff (2)'[UniqueID]))+0

Re: Calculate Working Hours excl Weekends & Holidays

Anonymous — Tue, 05 Jul 2022 09:13:28 GMT

Hi Anonymous

Create a measure like this :

WorkDays = VAR Calendar1 = CALENDAR(MAX('Table'[First Start Time]),MAX('Table'[Last Stop Time])) VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2)) VAR _hour1 = (COUNTX(FILTER(Calendar2,[WeekDay]<6),[Date])-5)*10 VAR _hour2 = if(hour(SELECTEDVALUE('Table'[Last Stop Time]))>=18,hour(SELECTEDVALUE('Table'[Last Stop Time])-SELECTEDVALUE('Table'[First Start Time]))-1,hour(SELECTEDVALUE('Table'[Last Stop Time])-SELECTEDVALUE('Table'[First Start Time]))) VAR _Minute=if(HOUR(SELECTEDVALUE('Table'[Last Stop Time]))>=18,60-MINUTE(SELECTEDVALUE('Table'[First Start Time])),MINUTE(SELECTEDVALUE('Table'[Last Stop Time])-SELECTEDVALUE('Table'[First Start Time]))) VAR _wholehour = _hour1+_hour2 VAR _diff = _wholehour& "h" &_Minute &"m" RETURN _diff

You will get a result like this :

I have attached my pbix file , you can refer to it .

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Calculate Working Hours excl Weekends & Holidays

tamerj1 — Tue, 28 Jun 2022 03:58:00 GMT

Hi Anonymous
I realized that there was a small mistake in the code where I used WEEKNUM function instead of using WEEKDAY. Here is the sample file updated https://we.tl/t-YYeFOgERsG

I doubled check the numbers manually and found accurate and the total is also accurate. It is also considering late start and early stop (these can be omitted if not required)

Working Hours = VAR Result = SUMX ( WorkingHours, VAR CurrentStartDate = WorkingHours[First Start Time] VAR CurrentStopDate = WorkingHours[Last Stop Time] VAR StandardStartDate = DATE ( YEAR ( CurrentStartDate ), MONTH ( CurrentStartDate ), DAY ( CurrentStartDate ) ) + TIME ( 8, 0, 0 ) VAR StandardEndDate = DATE ( YEAR ( CurrentStopDate ), MONTH ( CurrentStopDate ), DAY ( CurrentStopDate ) ) + TIME ( 18, 0, 0 ) VAR LateStartMinutes = IF ( CurrentStartDate > StandardStartDate, DATEDIFF ( StandardStartDate, CurrentStartDate, MINUTE ), 0 ) VAR EarlyStopMinutes = IF ( CurrentStopDate < StandardEndDate, DATEDIFF ( CurrentStopDate, StandardEndDate, MINUTE ), 0 ) VAR AllDays = CALENDAR ( CurrentStartDate, CurrentStopDate ) VAR AllWorkingDays = FILTER ( AllDays, NOT ( WEEKDAY ( [Date], 2 ) IN { 6, 7 } ) && NOT ( [Date] IN VALUES ( Holidays[Date] ) ) ) VAR WorkedMinutes = COUNTROWS ( AllWorkingDays ) * 600 VAR NetWorkedMinutes = WorkedMinutes - LateStartMinutes - EarlyStopMinutes RETURN NetWorkedMinutes ) RETURN FORMAT ( QUOTIENT ( Result, 60 ), "#,#" ) & " Hours & " & FORMAT ( MOD ( Result, 60 ), "00" ) & " Minutes"

Re: Calculate Working Hours excl Weekends & Holidays

tamerj1 — Tue, 28 Jun 2022 04:06:52 GMT

Hi Anonymous
Working hours are 10 not 8. I believe the the off days need to be considered automatically not manually. also the grand total in this case is of a great importance and cannot be just ignored.

Re: Calculate Working Hours excl Weekends & Holidays

Anonymous — Tue, 28 Jun 2022 05:19:03 GMT

Hi Anonymous

What are your working hours? 8 hours or 10 hours ? Also, is the holiday you're talking about your regular holiday or just for you ? Looking forward to your reply .

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Calculate Working Hours excl Weekends & Holidays

Anonymous — Wed, 29 Jun 2022 07:17:32 GMT

Hi Ailsa,

Our working hours is 8 hrs but I am considering 2 hrs as overtime. So need to get result with 10 hrs.

And the holidays are not the regular holidays, they are just for that particular occassion.

Re: Calculate Working Hours excl Weekends & Holidays

Anonymous — Wed, 29 Jun 2022 07:27:45 GMT

Hi Anonymous

Ok, then it is calculated according to 10 hours of working time, I will update my formula, you can refer to the following .

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.