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:
'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.
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
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.
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?
@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
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.
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
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.