cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dcs136
Advocate I
Advocate I

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

@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

 

View solution in original post

@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

View solution in original post

42 REPLIES 42

@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

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

@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

 

View solution in original post

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:

 

TimeFromTimeTo
01/01/2018 07:0001/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!

 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

Hello guys.

 

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

 

Thank you so much

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

@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

View solution in original post

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

 

 

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

 

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
Frequent Visitor

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?

PowerBI SLA.png

 

 

 

 

Rhndy
Frequent Visitor

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.

Herbert! You are a freaking LEGEND!

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

Anonymous
Not applicable

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

Problem Solved!

 

Thanks again Herbert!

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

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.