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

[rickylee]

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
 

[Stachu · Accepted solution]

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? 

[rickylee]

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

[Stachu]

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"

[rickylee]

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 

[rickylee]

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

[Stachu · Accepted solution]

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)

[LivioLanzo]

@rickylee

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

[Stachu · Accepted solution]

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?