Calculation of the process completion datetime from the start datetime of the process

[iadmitriy]

Dear specialists, good afternoon!

I had such a task (a sample is available at the link: https://cloud.mail.ru/public/RDhE/z1rYqn3Pt). There is a start date of some process or event (DateTime Start), and there is a planned number of hours for its execution (Duration in hours), based on this data, it is necessary to calculate the date time of the completion of this process (as a calculated column in the pbix file), but taking into account working-non-working days and working hours (Monday to Thursday from 9 to 18, Friday from 9 to 16, as well as the pre-holiday day is 1 hour shorter). In the pbix file, I have already created a calendar and a note about whether it is a working / non-working / weekend / or a shortened day

Has anyone faced a similar task?

Thank you in advance!

[iadmitriy · Accepted solution]

https://www.youtube.com/watch?v=2HkBbqxBzF0

[lbendlin]

DateTime Finish = 
var c = CALENDAR(Data[Date Start]+1,Data[Date Start]+60)
var d = ADDCOLUMNS(c,"dt",[Date])
var e = ADDCOLUMNS(d,"Duration",CALCULATE(sum('Calendar Issues'[WorkEnd])-sum('Calendar Issues'[WorkStart]),FILTER('Calendar Issues','Calendar Issues'[Date]=[dt])))
var f = ADDCOLUMNS(e,"cumulx",var df = [dt] return sumx(filter(e,[dt]<df),[Duration]))
var g = FILTER(f,Data[WorkedToday]+[cumulx]<=Data[Total Duration] && Data[WorkedToday]+[cumulx]+[Duration]>=Data[Total Duration])
return TOPN(1,SELECTCOLUMNS(g,"cd",[dt]+Data[Total Duration]-[cumulx]-Data[WorkedToday]+9/24))

This should do it - I changed the filter slightly to stop one workday ealier, and I also assumed that all workdays start at 9 am.

[lbendlin]

@iadmitriy nearly there.  Still need to do the backtracking part.

- created a calendar that looks 30 days ahead  (might need to be extended just to be sure, depends on your longest running activity/longest non-working scenario like christmas break)

- saved the [Date]  column so it can be used as a filter

- added the work duration for each day in the next 30 days

- created a running total of all work durations including the full current day

- filtered out all days where the running total is too small

- picked the first date from the remaining list.

This date is potentially a workday too far. So the last remaining step is to track back on the previous workday to find the exact completion time inside the working hours of that day.

[lbendlin]

Making some progress. Assumption is that any activity outside of declared business hours is ignored.

Since this is all time based I am using calculated columns throughout - unless you have any objections.

I added helper columns to express the start and end day fractions for each workday, taking into account the special rules for friday (two hours shorter). "WorkedToday"  goes through all possible scenarios and determines how much of the duration was covered on the first day of the activity.

Next step will be to create a table variable and do some  SUMX to determine when the duration is fully consumed. I am thinking of allocating twice the number of days just in case you have a freak streak of consecutive weekend/holiday dates.  Once the SUMX has overshot, i then need to backpedal to find the actual depletion timestamp.

[lbendlin]

@iadmitriy   You can simplify the MarkWorking calculated column

MarkWorking = COALESCE(LOOKUPVALUE('CalendarP'[Mark],'CalendarP'[Date],'Calendar Issues'[Date]),"Working")

Your question itself is intriguing, as it is the opposite of what is normally asked ("Number of working hours between two dates").  I have a certain approach in mind - i'll be back.

Before that however you need to define what to do when the start of the activity falls on a weekend, or outside the agreed working hours. Should the user be penalized, or should the hour count start at the first working period after the weekend?

Note:  Your sample transaction data does not cover the Working Short or Weekend Postroned (sic) days, so that cannot be tested unless you provide more complete sample data.

[iadmitriy]

If the process started during non-working hours, the timer will start ticking during working hours. For example, if the process started on 13.08.2021 (Friday is a working day until 16: 00) at 16: 30, then the timer will start ticking from 16.08.2021 9: 00; or the process started on 22.02.2021 17: 02 (this is a pre-holiday day, which means an hour shorter and 23.02.2021 is a holiday), then the timer will start ticking from 24.02.2021 9: 00

[iadmitriy]

I was able to calculate the end date of the timer, but with an accuracy of up to an hour (I added the file with my solution via the link), I understood that the code somehow resembles your solution. But this is not enough, it is necessary to be accurate to the minute. Well, the problem is that there will be quite a lot of lines.

[lbendlin]

My solution is accurate to the minute, but only if the start timestamp falls into regular work hours. Otherwise the result will always be at the hour mark, unless your duration has deeper than hour granularity.

[learner03]

@lbendlin Can I have your pbix as I have similar problem-

https://community.powerbi.com/t5/Desktop/Calculate-Completion-Date-based-on-work-hours/m-p/3091593

[iadmitriy]

Hello! It is as link for download my solution https://cloud.mail.ru/public/Uv2r/VrHxq3fB4