Calculate Date and Time difference considering the weekends and workhours

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

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?

Hi Anonymous,

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

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)

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?

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.

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.