Calculate Date and Time difference considering the weekends and workhours

[dcs136]

  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.

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!

[v-haibl-msft · Accepted solution]

@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.

Best Regards,

Herbert

[dcs136]

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.

[v-haibl-msft]

@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?

Best Regards,

Herbert

[dcs136]

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
)

[v-haibl-msft · Accepted solution]

@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.

Best Regards,

Herbert

[RicardoAlfa]

I have been working with this code, but is possible agree a new column to limit the TimeFrom date, and it can considering to the final result in hours

For example:

TimeFrom	TimeTo
01/01/2018 07:00	01/01/2018 10:00

My Result is 3 hours, but it should be 2 hours, considering the business hours in the day 08:00 am to 18:00 pm, May be I must agree a column like the LastDayStartTime, but using the First Star Time.

Some Insight?

Thanks!

[hmedinaa]

 I have the same issue, wondering if we are missing a FirstDayStartTime, if the starting time is before 8am  (7:00:00AM) and finish time is 10:00:00 AM is giving me has result the regular time diff, = 3hrs

[omarevp]

Hello guys.

Is there a way to add a function that excludes the holidays and weekends?

Thank you so much

[dcs136]

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

 

[v-haibl-msft · Accepted solution]

@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

[ballist1x]

@v-haibl-msft

Hi Herbert, can you provide some more asissaance here?

the forumla doesnt work if the firstdate is before the SLA time,

for example if the DateTimeFrom is 7AM and the SLA start time is 9AM and the finish time is 10AM the working hours should be 1 but the current calculation = 3.

We need to add in a new Calculated column called

FirstdayStartTime

FirstDayStartTime =
DATE ( YEAR ( Table1[DateTimeFrom] ), MONTH ( Table1[DateTimeFrom] ), DAY ( Table1[DateTimeFrom] ) )
& " 09:00:00"

So therefore the FirstDaySecDiff would have to be modified to include this from;

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
    )
))

to something that includes FirstDayStartTime  - please can you help?

[maartjedutchy]

Hi guys!

I have made a few tweaks to @v-haibl-msft file, see here my updated version .

I had issues with some posts;

- A post which was received before openingtime on the same day (LastDayStartTime) for example at 02:00 AM and was picked up at 7:15 AM (My LastDayStarttime is 07:00 AM) then the response time should be 15 minutes.  I have added a column FriDayEndMidNight which checks if the post was received on the same day but before LastDayStartTime (I added calculations in the FirstDaySecDiff Column). This now works.

- I also wanted to include Public Holidays. Sometimes my customer care teams are closed for example on a Monday or Tuesday and it should skip those days in the calculations. So I have loaded in an Excel sheet in which I enter the Holidays (see table 'Holidays' in PBI) and related that table to the Calendar table. Then I check in the WorkDay column in Calendar if the date exists in the Holidays file, if TRUE then Return FALSE.

- Then I have made some changes to the FirstDaySecDiff column to skip calculated the diff if WorkDay is False. 

- I also made a relationship between DateTimeFrom and Calendar so I could use the related value.

- Furthermore sometimes my team picks up a post outside workinghours, I feel like I need to draw the line there so I won't calculate response time over those posts. However I have added a column in my table that checks whether a post was picked up inside or outside working hours, so I can apply that as a filter to my graphs.

Thanks again Herbert! My DAX skills are quite limited so your file really helped me out.

I know some people had issues with posts published before openingtime on the same day, this file should help.

Thanks!

Maartje

[ballist1x]

@maartjedutchy

Hi, thanks for your help. i just cant make this work if the time difference between the start date and end date is over 7 days

for example, if i use:

First Day Start Time 09:00, First Day End Time :17:30

Last Day Start Time 09:00, Last Day End Time :17:30

If i use two examples, neither calculate properly:

Datetimefrom 22/6/2018 17:25

DateTimeTo 25/6/2018 09:10

Expected difference 15 minutes, TotsalMinDiff = 10 mins

Datetimefrom 18/6/2018 13:05

DateTimeTo 27/6/2018 17:05

Expected difference 3810 minutes, TotsalMinDiff = 4085 mins

do you see a similar pattern?

[maartjedutchy]

Hi @ballist1x,

What are the working days from your team? Is it 7 days a week 09:00 - 17:30?

Let me know and I will have a look.

Thanks,

M

[ballist1x]

Hi @maartjedutchy

we got it sorted in the end, but had to take into account several other parameters as well. For example, an event could start at weekend and alos be actioned by the team at weekend our outside of SLA hours so the firstdaysec diff would be more like:

FirstDaySecDiff = IFERROR(
 IF( //Created on Weekend, Assigned after 9AM Monday
  '2018'[CreatedWorkDay] = FALSE()
  && '2018'[Assigned] > '2018'[FollowingMondayStartTime],0,
 IF( //Created on Weekend, Assigned before 9AM Monday
  '2018'[CreatedWorkDay] = FALSE()
  && '2018'[Assigned] < '2018'[FollowingMondayStartTime], 0,
 IF( //Created on Weekend, Assigned on Weekend
  '2018'[CreatedWorkDay] = FALSE()
  && '2018'[AssignedWorkDay] = FALSE(), 0,
    IF(
  '2018'[CreatedWorkDay] = FALSE()
  && '2018'[Created] < '2018'[FirstDayStartTime],
    DATEDIFF('2018'[FirstDayStartTime], '2018'[Assigned], SECOND),
    IF(
     '2018'[FirstDayEndTime] >= '2018'[Created]
        && '2018'[FirstDayEndTime] <= '2018'[Assigned],
    DATEDIFF ( '2018'[Created], '2018'[FirstDayEndTime], SECOND ),
    IF (
        '2018'[FirstDayEndTime] >= '2018'[Created]
            && '2018'[FirstDayEndTime] > '2018'[Assigned],
        DATEDIFF ( '2018'[Created], '2018'[Assigned], SECOND ),
        0
    )))))),
0)

[Rhndy]

Hi @maartjedutchy,

I've tried your solution but it doesn't work properly. 

Service window =  Workdays from 07:30 to 17:00 (NL-time)

I have the following item:

Date/Time ticket created = 6/7/2018 on 9:31 

Date/Time ticket closed = 11/7 on 11:22

Calculation by hand:

6/7 - 7 hours, 29 minutes (9:31 - 17:00)

7/7 - 0 hours

8/7 - 0 hours

9/7 - 9 hours, 30 minutes (7:30-17:00)

10/7 - 9 hours, 30 minutes (7:30-17:00)

11/7 - 3 hours, 52 minutes  (7:30-11:22)

Total is 30 hours,  21minutes or 1821 minutes

Your calculation give me a TotalMinDiff of 1432. See attached picture.

Something seems off, any idea?

[Rhndy]

i think i found out what's wrong here:

- The middayssecdiff has the servicehours hard-coded (3600 * 10). My window is 9,5 hours, by changing it to 3600 * 9.5 that was allright and got a small deviation fixed.

- The formula for FirstDaySecDiff produces 0 when the ticket hasn't been resolved the same day. That's wrong.

The formula in your file:

IF (
'SLA'[FirstDayEndTime] >= 'SLA'[DateTimeFrom]
&& 'SLA'[FirstDayEndTime] <= 'SLA'[DateTimeTo] && RELATED('Calendar'[WorkDay]) = TRUE ();
DATEDIFF ( 'SLA'[DateTimeFrom]; 'SLA'[FirstDayEndTime]; SECOND );

Fails on the RELATED('Calendar'[WorkDay]) = TRUE ();

When turning that off, it works. But it doesn't when a ticket got created in the weekend. As we speak i'm checking to fix that.

[Anonymous]

I changed a view things to work with regionalization a little bit easier.

In the time calculations dont use & "18:00:00" because then PowerBI has to interpret a text to a date which is depending on the regional settings. Instead just add the time as datetime object like so: + TIME(18,0,0).

When you do it like that you will always receive an datetime object and its easy (and propably faster) to interpret for PowerBI.

I also used a public holiday calendar and imported it to another table and created a * to Many relationship between the calendar.date and the hollidays.date.  My Calculated Column in Calendar now checks if it was a holiday:

Holliday = CALCULATE( MIN(Hollidays[HollidayName]), ALLEXCEPT('Calendar','Calendar'[Date]))

and then you can check if ( isblank(holliday)) which will give you if the date was a holiday or not. I added to the workday check if the day was a holliday or not.

[maartjedutchy]

Herbert! You are a freaking LEGEND!

[Karen_Payne]

Thank you very much for sharing!  This is exactly what I was needing.

[Anonymous]

Thumbs up. This was exactly what I needed also. Thank you for contributing!

[dcs136]

Problem Solved!

Thanks again Herbert!