cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper III

## Working hours without non-working hours, no weekends and no holidays

Hello MS expert,

I have a requirement that I need to find out the difference in hour between date_started and date_submitted such as the following table:

 assessment_id date_started date_submitted AID047384 01/11/2018 14:33 02/11/2018 16:45 AID047550 01/11/2018 14:34 AID047380 01/11/2018 14:37 01/11/2018 17:29 AID047494 01/11/2018 14:37 01/11/2018 16:14 AID047604 01/11/2018 14:39 01/11/2018 15:40 AID047448 01/11/2018 14:39 05/11/2018 09:12 AID047568 01/11/2018 14:41 AID047612 01/11/2018 14:42 01/11/2018 15:59

The requirement is only included the working hours.
Working hour is 9-17
No weekends
No holidays, the rest of the holiday in 2018 (U.K) is

 25 December Tuesday Christmas Day 26 December Wednesday Boxing Day

I followed this link to do but it seems it does not allow 'blank' value.
https://community.powerbi.com/t5/Desktop/Calculating-Working-hours/td-p/374255

Also, how do I account for holiday like Christmas day/boxing day?

Thanks,
Ricky

2 ACCEPTED SOLUTIONS
Community Champion

for blanks I assume NOW() and TODAY() would work fine, is that correct?
as for the holidays - the easiest is to have a separate calendar table, where you mark holidays & weekends as 0, and working days as 1

the idea is following:
1) we count working days between the 2 points in time - DaysInScope
2) we calculate the time difference that can be negative if e.g. something was started on the evening of day 1 and submited on the morning of day 2 - HourDifference - should work fine as long as times are only between 9-17 range
3) we multiply the days by working hours and add actual time difference

```Column =
VAR ComparisonDate =
IF ( ISBLANK ( 'Table'[date_submitted] ), TODAY (), 'Table'[date_submitted] )
VAR ComparisonTime =
TIMEVALUE (
IF ( ISBLANK ( 'Table'[date_submitted] ), NOW (), 'Table'[date_submitted] )
)
VAR DaysInScope =
FILTER (
'Calendar',
'Calendar'[Date] >= INT ( 'Table'[date_started] )
&& 'Calendar'[Date] < INT ( ComparisonDate )
&& 'Calendar'[WorkingDays] = 1
)
VAR HourDifference =
IF (
ComparisonTime >= TIMEVALUE ( 'Table'[date_started] ),
VALUE ( ComparisonTime - TIMEVALUE ( 'Table'[date_started] ) ),
- VALUE ( ComparisonTime - TIMEVALUE ( 'Table'[date_started] ) )
)
* 24
RETURN
COUNTROWS ( DaysInScope ) * 8
+ HourDifference```

EDIT - I just noticed that for

 AID047380 01/11/2018 14:37 01/11/2018 17:29

submitted time is 17:29 - does that mean that time should be calculated from 17:00, skipping the 29 min of overtime?

Thank you for the kudos 🙂

Community Champion

hi @rickylee
this should work

```Column =
VAR StartedTime = MAX(MIN(TIMEVALUE('Table'[date_started]),17/24),9/24)
VAR SubmittedTime = MAX(MIN(TIMEVALUE('Table'[date_submitted]),17/24),9/24)
VAR DaysInScope = FILTER('Calendar','Calendar'[Date]>=INT('Table'[date_started]) && 'Calendar'[Date]< INT('Table'[date_submitted]) && 'Calendar'[WorkingDays]=1)
VAR HourDifference = IF(SubmittedTime>=StartedTime,VALUE(SubmittedTime-StartedTime),-VALUE(SubmittedTime-StartedTime))*24
RETURN
IF(ISBLANK('Table'[date_submitted]), BLANK(), COUNTROWS(DaysInScope)*8+HourDifference)```

Thank you for the kudos 🙂

7 REPLIES 7
Helper III

Hello @Stachu,

Thanks for your superme coding skill. I only realise there are other ways to interpret the working hours. One working day has 8 hours only. 9-17 is an example, someone may work between 10-18. But it is still 8 hour per day.

I tried to change your code to fit into that requirement but no joys.

Just to reinstate the requirement: (Everything is the same apart from the working hour is now 8 hours per day rather than 9-17)
Working hour is 8 hours per day
Blank date should not be in calculation.
No weekends
No holidays, the rest of the holiday in 2018 (U.K) is

 25 December Tuesday Christmas Day 26 December Wednesday Boxing Day

Thanks,
Ricky

Community Champion

this should work fine

```Column 2 =
VAR StartedTime =
TIMEVALUE ( 'Table'[date_started] )
VAR SubmittedTime =
TIMEVALUE ( 'Table'[date_submitted] )
VAR DaysInScope =
FILTER (
'Calendar',
'Calendar'[Date] >= INT ( 'Table'[date_started] )
&& 'Calendar'[Date] < INT ( 'Table'[date_submitted] )
&& 'Calendar'[WorkingDays] = 1
)
VAR HourDifference = ( SubmittedTime - StartedTime )
* 24
VAR HourDifferenceCapped =
SIGN ( HourDifference ) * MIN ( ABS ( HourDifference ), 8 )
RETURN
IF (
ISBLANK ( 'Table'[date_submitted] ),
BLANK (),
COUNTROWS ( DaysInScope ) * 8
+ HourDifferenceCapped
)```

it may give 0 in case someone is doing overtime (see column value for b example)

```let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZBLDoMwDAWvgrJGwk7sfLxD6qZnQCyg979DgyqoZcx24nl+zrKE+f0CKqlSGAPghDhFwDogSUoHin+UhTis4+kww905YgY1lKozVAwqEptyqDllrJMFSTkZHKcZxEKg91B9cPhC0ASjPjrfHcKOdJdu2MXczGd27Ze7eQs7yheKcHbYvYeOitP385DLNnf9Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [assessment_id = _t, date_started = _t, date_submitted = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"assessment_id", type text}, {"date_started", type datetime}, {"date_submitted", type datetime}})
in
#"Changed Type"```

Thank you for the kudos 🙂

Helper III

Hello @Stachu,

I send this as a private message because this is not related to the question I am asking in forum. I noticed you have a very good coding skill. I am not a developer but need to use power bi to create report and dashboard. My experience about Power bi is a few months.

Due to the 'working day' problem, I realise coding/programming in DAX is very important. How did you start that? I don't have a particular course to give me a head-start at all. I googled all the functions you used and tried to understand your logic behind. It was a very slow process and there are some logic I still don't understand but I can apply to it.

Thanks,
ricky

Helper III

Hi @Stachu,

Thank you. The requirement is like this:
If it is a blank cell (e.g. submitted date), no calculation is needed because no one submits an application. Is it possible to do it like this?

For your question regarding to a time 17:29, I only need to consider 9-17, so 29mins should not be considered in the calculation.

Thank you.

Kind regards,
Ricky

Community Champion

hi @rickylee
this should work

```Column =
VAR StartedTime = MAX(MIN(TIMEVALUE('Table'[date_started]),17/24),9/24)
VAR SubmittedTime = MAX(MIN(TIMEVALUE('Table'[date_submitted]),17/24),9/24)
VAR DaysInScope = FILTER('Calendar','Calendar'[Date]>=INT('Table'[date_started]) && 'Calendar'[Date]< INT('Table'[date_submitted]) && 'Calendar'[WorkingDays]=1)
VAR HourDifference = IF(SubmittedTime>=StartedTime,VALUE(SubmittedTime-StartedTime),-VALUE(SubmittedTime-StartedTime))*24
RETURN
IF(ISBLANK('Table'[date_submitted]), BLANK(), COUNTROWS(DaysInScope)*8+HourDifference)```

Thank you for the kudos 🙂

Solution Sage

@rickylee

here is another way you can do it: https://1drv.ms/u/s!AiiWkkwHZChHjzMmijod8MZ0FWZn

Proud to be a Datanaut!

Community Champion

for blanks I assume NOW() and TODAY() would work fine, is that correct?
as for the holidays - the easiest is to have a separate calendar table, where you mark holidays & weekends as 0, and working days as 1

the idea is following:
1) we count working days between the 2 points in time - DaysInScope
2) we calculate the time difference that can be negative if e.g. something was started on the evening of day 1 and submited on the morning of day 2 - HourDifference - should work fine as long as times are only between 9-17 range
3) we multiply the days by working hours and add actual time difference

```Column =
VAR ComparisonDate =
IF ( ISBLANK ( 'Table'[date_submitted] ), TODAY (), 'Table'[date_submitted] )
VAR ComparisonTime =
TIMEVALUE (
IF ( ISBLANK ( 'Table'[date_submitted] ), NOW (), 'Table'[date_submitted] )
)
VAR DaysInScope =
FILTER (
'Calendar',
'Calendar'[Date] >= INT ( 'Table'[date_started] )
&& 'Calendar'[Date] < INT ( ComparisonDate )
&& 'Calendar'[WorkingDays] = 1
)
VAR HourDifference =
IF (
ComparisonTime >= TIMEVALUE ( 'Table'[date_started] ),
VALUE ( ComparisonTime - TIMEVALUE ( 'Table'[date_started] ) ),
- VALUE ( ComparisonTime - TIMEVALUE ( 'Table'[date_started] ) )
)
* 24
RETURN
COUNTROWS ( DaysInScope ) * 8
+ HourDifference```

EDIT - I just noticed that for

 AID047380 01/11/2018 14:37 01/11/2018 17:29

submitted time is 17:29 - does that mean that time should be calculated from 17:00, skipping the 29 min of overtime?

Thank you for the kudos 🙂