cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
omarevp Regular Visitor
Regular Visitor

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

Hello guys.

 

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

 

Thank you so much

RicardoAlfa Frequent Visitor
Frequent Visitor

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

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!

tmears Member
Member

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

wonder if anyone could help?  I have the following:

 

MidDaysSecDiff =
IF (
    FORMAT ( msdyn_audithistories[Start time], "Short Date" )
        <> FORMAT ( msdyn_audithistories[createdon], "Short Date" ),
    3600 * 10
        * (
            CALCULATE (
                DISTINCTCOUNT ( 'Calender'[Date] ),
                FILTER (
                    'Calender',
                    'Calender'[Date] > msdyn_audithistories[FirstDayEndTime]
                        && 'Calender'[Date] < msdyn_audithistories[LastDayStartTime]
                        && 'Calender'[WorkDay] = TRUE ()
                )
            )
                - 1
        ),
    0
)

 

 

However i need to state that if the msdyn_audithistories[Start time] ie the start time is blank that it returns 0? any pointers?

 

Many thanks 

 

 

Highlighted
hmedinaa Frequent Visitor
Frequent Visitor

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

 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

maartjedutchy Frequent Visitor
Frequent Visitor

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

Herbert! You are a freaking LEGEND!

ballist1x Member
Member

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

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

Re: 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

ballist1x Member
Member

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

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

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

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

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

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)