cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dcs136 Frequent Visitor
Frequent Visitor

Calculate Date and Time difference considering the weekends and workhours

  Hi everybody!

 

I'm still learning how to use Power BI, I search for this everywhere but I didn't found an answer!

Here is the thing,

 

I need to calculate the difference between dates and time, but the thing is that I need to calculate just the working days and the workhours.

Sem título.jpg

Taking the first line as an example: (03/11/2016 13:57:22 - 03/01/201613:36:38) the column hours should be 63:38:38

Considering that 03/05 and 06/05 are saturday and sunday, and considering that the work hours are from 08:00AM to 18:00PM.

 

How can I solve that!

 

Thank you!

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-haibl-msft Super Contributor
Super Contributor

Re: Calculate Date and Time difference considering the weekends and workhours

@dcs136

 

The only possibility I can think of is that there is something wrong with the Calendar'[WorkDay] formula. Could you please check this column formula is like below? The data type of this column should be “True/False”.

If it is actually same as mine, could you please upload your .pbix file to OneDrive and share it with me? In that case I can take a look at your .pbix file and try to solve the problem.

 

Calculate Date and Time difference considering the weekends and workhours_1.jpg

 

Best Regards,

Herbert

 

v-haibl-msft Super Contributor
Super Contributor

Re: Calculate Date and Time difference considering the weekends and workhours

@dcs136

 

How about the result if we update the “FirstDaySecDiff” measure as below?

 

FirstDaySecDiff = 
IF (
    Table1[FirstDayEndTime] >= Table1[DateTimeFrom]
        && Table1[FirstDayEndTime] <= Table1[DateTimeTo],
    DATEDIFF ( Table1[DateTimeFrom], Table1[FirstDayEndTime], SECOND ),
    IF (
        Table1[FirstDayEndTime] >= Table1[DateTimeFrom]
            && Table1[FirstDayEndTime] > Table1[DateTimeTo],
        DATEDIFF ( Table1[DateTimeFrom], Table1[DateTimeTo], SECOND ),
        0
    )
)

 

Best Regards,

Herbert

39 REPLIES 39
Highlighted
v-haibl-msft Super Contributor
Super Contributor

Re: Calculate Date and Time difference considering the weekends and workhours

@dcs136

 

There may be several methods to get the expected result. I’ll divide the difference to three parts, the first day, the middle days and the last day. For details, please refer to following steps.

I’ve also upload my .pbix file here for reference.

 

  1. Create a calendar table with following formula. But do not create relationship between these two tables.
    Calendar =
    CALENDAR ( "1/1/2016", "12/31/2016" )
  2. Create a column in calendar table to mark the working days.
    WorkDay = 
    VAR WeekDayNum =
        WEEKDAY ( 'Calendar'[Date], 2 )
    RETURN
        (
            IF ( WeekDayNum = 6 || WeekDayNum = 7, FALSE (), TRUE () )
        )
  3. Create a column to store the working end time of first day.
    FirstDayEndTime = 
    DATE ( YEAR ( Table1[DateTimeFrom] ), MONTH ( Table1[DateTimeFrom] ), DAY ( Table1[DateTimeFrom] ) )
    & " 18:00:00"
  4. Create a column to calculate the working seconds of first day.
    FirstDaySecDiff = 
    DATEDIFF ( Table1[DateTimeFrom], Table1[FirstDayEndTime], SECOND )
  5. Create a column to store the working start time of last day.
    LastDayStartTime = 
    DATE ( YEAR ( Table1[DateTimeTo] ), MONTH ( Table1[DateTimeTo] ), DAY ( Table1[DateTimeTo] ) )
    & " 8:00:00"
  6. Create a column to store the working end time of last day.
    LastDayEndTime = 
    DATE ( YEAR ( Table1[DateTimeTo] ), MONTH ( Table1[DateTimeTo] ), DAY ( Table1[DateTimeTo] ) )
    & " 18:00:00"
  7. Create a column to calculate the working seconds of last day.
    LastDaySecDiff = 
    IF (
        FORMAT ( Table1[DateTimeFrom], "Short Date" )
            <> FORMAT ( Table1[DateTimeTo], "Short Date" ),
        IF (
            Table1[DateTimeTo] >= Table1[LastDayStartTime]
                && Table1[DateTimeTo] <= Table1[LastDayEndTime],
            DATEDIFF ( Table1[LastDayStartTime], Table1[DateTimeTo], SECOND ),
            IF (
                Table1[DateTimeTo] > Table1[LastDayEndTime],
                DATEDIFF ( Table1[LastDayStartTime], Table1[LastDayEndTime], SECOND ),
                0
            )
        ),
        0
    )
  8. Create a column to calculate the working seconds of middle days.
    MidDaysSecDiff = 
    IF (
        FORMAT ( Table1[DateTimeFrom], "Short Date" )
            <> FORMAT ( Table1[DateTimeTo], "Short Date" ),
        3600 * 10
            * (
                CALCULATE (
                    DISTINCTCOUNT ( 'Calendar'[Date] ),
                    FILTER (
                        'Calendar',
                        'Calendar'[Date] > Table1[FirstDayEndTime]
                            && 'Calendar'[Date] < Table1[LastDayStartTime]
                            && 'Calendar'[WorkDay] = TRUE ()
                    )
                )
                    - 1
            ),
        0
    )
  9. Create the final column to calculate the total working hours.
    TotalHourDiff = 
     ( Table1[FirstDaySecDiff] + Table1[LastDaySecDiff]
        + Table1[MidDaysSecDiff] )
    / 3600

Note: The data type of columns of “FirstDayEndTime”, “LastDayStartTime” and “LastDayEndTime” should be Date/Time as below.

Calculate Date and Time difference considering the weekends and workhours_1.jpg

 

Best Regards,

Herbert

dcs136 Frequent Visitor
Frequent Visitor

Re: Calculate Date and Time difference considering the weekends and workhours

Herbert,

 

Thank you so much for your help.

 

But unfortunately DATEDIFF  gives an error "In DATEDIFF function, the start date cannot be greater than the end date"

because some hours on "DateTimeFrom" are greater than the "FirstDayEndTime" as you can see on the picture

 

I tried to fix this, but I couldn't do it.

 

Thank you!Error.jpg

v-haibl-msft Super Contributor
Super Contributor

Re: Calculate Date and Time difference considering the weekends and workhours

@dcs136

 

Please update the DAX formula of FirstDaySecDiff column as below and have a try again.

 

FirstDaySecDiff = 
IF (
    Table1[FirstDayEndTime] >= Table1[DateTimeFrom],
    DATEDIFF ( Table1[DateTimeFrom], Table1[FirstDayEndTime], SECOND ),
    0
)

Best Regards,

Herbert

dcs136 Frequent Visitor
Frequent Visitor

Re: Calculate Date and Time difference considering the weekends and workhours

Herbert,

 

Thanks again for your help and sorry for bothering you again,

 

The last problem was solved, but I still have a problem with the MidDaysSecDiff column.

As you can see on the screen below, It gives an erro saying that DAX can't compare date values with True/False values.

 

Error.jpg

v-haibl-msft Super Contributor
Super Contributor

Re: Calculate Date and Time difference considering the weekends and workhours

@dcs136

 

It seems there are extra expression in your formula screenshot as below. How about the result if you remove it?

If problem persits, could you please give a complete screenshot for this MidDaysSecDiff formula?

 

Calculate Date and Time difference considering the weekends and workhours_1.jpg

 

Best Regards,

Herbert

dcs136 Frequent Visitor
Frequent Visitor

Re: Calculate Date and Time difference considering the weekends and workhours

Hi Herbert!

 

I removed this line but the problem still remains!

 

I compared the both codes and they are the same, as you can see below

 

MidDaysSecDiff =
IF (
    FORMAT ( [DateTimeFrom]; "Short Date" )
        <> FORMAT ( [DateTimeTo]; "Short Date" );
    3600 * 10
        * (
            CALCULATE (
                DISTINCTCOUNT ( 'Calendar'[Date] );
                FILTER (
                    'Calendar';
                    'Calendar'[Date] > [FirstDayEndTime]
                        && 'Calendar'[Date] < [LastDayStartTime]
                        && 'Calendar'[WorkDay] = TRUE ()
                )
            )
                - 1
        );
    0
)

 

Error.jpg

v-haibl-msft Super Contributor
Super Contributor

Re: Calculate Date and Time difference considering the weekends and workhours

@dcs136

 

The only possibility I can think of is that there is something wrong with the Calendar'[WorkDay] formula. Could you please check this column formula is like below? The data type of this column should be “True/False”.

If it is actually same as mine, could you please upload your .pbix file to OneDrive and share it with me? In that case I can take a look at your .pbix file and try to solve the problem.

 

Calculate Date and Time difference considering the weekends and workhours_1.jpg

 

Best Regards,

Herbert

 

dcs136 Frequent Visitor
Frequent Visitor

Re: Calculate Date and Time difference considering the weekends and workhours

Hebert!

 

Thanks again!

 

It was exactly what you said.

 

The column Workday was duplicate.

 

Now it's working perfect!!

 

 

dcs136 Frequent Visitor
Frequent Visitor

Re: Calculate Date and Time difference considering the weekends and workhours

Hello Herbert,

 

I need a little help here again.

 

I tried to solve it but I couldn't find an solution.

 

As you can see on the picture bellow, the calculation between two dates is correclty just when the date is different. But when the difference is just between hours the result is the DateTimFrom column - FirstDayEndTime Column.

 

For example:

03/01/2016 10:50:42  = 7,155 - but the result should be 2,046

 

 Test.jpg