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

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

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

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

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.

Duffkess Regular Visitor
Regular Visitor

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

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.

sam_woolerton Frequent Visitor
Frequent Visitor

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

Really appreciate the work y'all put in to get a working DAX solution. I turned it into a custom M function so that you can easily apply it to multiple columns without having a ton of duplication. Also fixed a couple of small bugs that were in the original DAX.

 

To use, just copy this into a new blank query and should work out of the box.

Of course you're free to change the start/end times (currently at 8am and 5pm) etc

 

(from, to) => let
    // utility functions
    dateWithSetTime = (dt, t) => DateTime.From(Date.ToText(Date.From(dt)) & " " & Time.ToText(t)),
    max2 = (first, second) => if first > second then first else second,
    min2 = (first, second) => if first < second then first else second,
    isBetween = (t, first, last) => t >= first and t <= last,
    isWeekend = (day) => Date.DayOfWeek(day) = 5 or Date.DayOfWeek(day) = 6,
    isWorkday = (day) => not isWeekend(day),
    numDaysBetween = (f, t) =>
        let
            start = Date.AddDays(Date.From(from), 1),
            length = Duration.Days(Date.From(to) - Date.From(from)) - 1,
            dates = List.Dates(start, length, #duration(1, 0, 0, 0)),
            workdays = List.Select(dates, each isWorkday(_))
        in
            List.Count(workdays),
    dur = (days) => Duration.From(days),
    
    // variables and initial setup 
    startTime = #time(8,0,0),
    endTime = #time(17,0,0),
    firstDayStartTime = dateWithSetTime(from, startTime),
    firstDayEndTime = dateWithSetTime(from, endTime),
    lastDayStartTime = dateWithSetTime(to, startTime),
    lastDayEndTime = dateWithSetTime(to, endTime),
    endsOnStartDay = Date.From(from) = Date.From(to),
    
    // get seconds for first, middle, and last days
    firstDaySeconds =
        // make sure that end date is after start date 
        if from > to then error "The start date is greater than the end date. Make sure that you passed the arguments in the right order." else
        // record 0 time if weekend, starts after day ends, or ends before day starts
        if isWeekend(Date.From(from)) or from > firstDayEndTime or to < firstDayStartTime
        then dur(0)
        else min2( to, firstDayEndTime ) - max2( from, firstDayStartTime ),
        
    middleDaysSeconds =
        if endsOnStartDay
        then dur(0)
        else (endTime - startTime) * numDaysBetween(from, to),
    
    lastDaysSeconds =
        // record 0 time if holiday, weekend, already counted, or ends before day starts 
        if isWeekend(Date.From(to)) or endsOnStartDay or to < lastDayStartTime 
        then dur(0)
        else min2(to, lastDayEndTime) - lastDayStartTime,
    
    totalSeconds = Duration.TotalSeconds(firstDaySeconds + middleDaysSeconds + lastDaysSeconds) 
in
    totalSeconds / 3600
 
Pro tip: enable M syntax highlighting in Preview features options, it makes it so much more pleasant to write in.
itshudak Frequent Visitor
Frequent Visitor

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

Sam,

 

I am actually running into an issue on some of the output.

FirstStartDateTime: 10/7/2017 1:20:00 PM

CreateDate: 10/10/2017 8:39:18 AM

 

An error occurred in the ‘’ query. Expression.Error: The 'increment' argument is out of range.
Details:
1.00:00:00

 

I'm not sure what is happening here. I kept everything the same, and also changed the startTime, but am getting the same error. Any ideas?

sam_woolerton Frequent Visitor
Frequent Visitor

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

@itshudak looks like you passed the arguments in the wrong order (doing that reproduced the exact error for me).

I've updated my code above to give a helpful error message in that case

itshudak Frequent Visitor
Frequent Visitor

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

Reversing the inputs actually gives me the same error, but 10 times worse. The output should be positive, correct? Regardless, I don't know what this error is actually saying, but my data looks correct, so I don't think it's a formatting issue. I'm just confused as to what needs to be fixed to get it to work properly. I have 100k rows that work, but these 100 or so errors have got me for a loop.

sam_woolerton Frequent Visitor
Frequent Visitor

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

@itshudak can you put together a reproducible example? Check this out for pointers

Easiest way would be to put together a CSV with 2 columns (start and end time) and 5-10 rows, made up from your problem rows. Import this into Power BI and make sure you still get the error

 

I'll then check it out and see what I can do

itshudak Frequent Visitor
Frequent Visitor

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

Nevermind, consider this egg on my face. It was actually an issue with the stop time being before the start time and I was just getting mixed up. Thank you for your help with this, I appreciate it.

Highlighted
lianetoohey Regular Visitor
Regular Visitor

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

@sam_woolerton this is fantastic, thank you so much! I did have to make one small change to get the hours to calculate properly - using the default Date.DayofWeek settings, the weekends are days 0 and 6 rather than 5 and 6.