Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
rickylee
Helper III
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_iddate_starteddate_submitted
AID04738401/11/2018 14:3302/11/2018 16:45
AID04755001/11/2018 14:34 
AID04738001/11/2018 14:3701/11/2018 17:29
AID04749401/11/2018 14:3701/11/2018 16:14
AID04760401/11/2018 14:3901/11/2018 15:40
AID04744801/11/2018 14:3905/11/2018 09:12
AID04756801/11/2018 14:41 
AID04761201/11/2018 14:4201/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 DecemberTuesdayChristmas Day
26 DecemberWednesdayBoxing 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
Stachu
Community Champion
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 

AID04738001/11/2018 14:3701/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? 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

Stachu
Community Champion
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)


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

View solution in original post

7 REPLIES 7
rickylee
Helper III
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 DecemberTuesdayChristmas Day
26 DecemberWednesdayBoxing Day


Thanks,
Ricky

Stachu
Community Champion
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"





Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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
Helper III
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

Stachu
Community Champion
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)


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

LivioLanzo
Solution Sage
Solution Sage

@rickylee

 

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Stachu
Community Champion
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 

AID04738001/11/2018 14:3701/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? 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.