Backward scheduling based on working time DAX

[MMZTM]

Hello everyone,

im trying to create a calculated Column by DAX that will give me the Completion-Date for every Job-No using the tables shown below. 

Based on the Delivery-Date of the Order-Table, the Completion-Dates for the respective Job are to be scheduled backward based on the needed Time for every Job. In addition, scheduling should take into account working hours and weekends.

I already have a simple scheduling based on days and without considering working hours and weekends (Code below) - Thanks to @tamerj1 . 

For example for Order-No. 111 the results for every Job-No should be:

Job-No. 4: Completion-Date = 14.08.2023 12:00 AM (Same as Delivery-Date)

Job-No. 3: Completion-Date = 11.08.2023 03:59 PM (Because of the Weekend)

Job-No. 2: Completion-Date = 11.08.2023 11:12 AM

Job-No. 1: Completion-Date = 09.08.2023 03:12 PM 

It would be great if someone could help me.

Thanks a lot in advance!

 

 

 

Completion Date = 
    VAR CurrentOrderTable = CALCULATETABLE(OPERATION_TABLE, ALLEXCEPT(OPERATION_TABLE, OPERATION_TABLE[Operation-No.]))

    VAR TableAfter = FILTER(CurrentOrderTable, OPERATION_TABLE[Job-No.] > EARLIER(OPERATION_TABLE[Job-No.]))

    VAR NumberOfDays = SUMX( TableAfter, OPERATION_TABLE[Time in days])

    VAR Delivery = RELATED(ORDER_TABLE[DELIVERY])

    RETURN Delivery - NumberOfDays

[lbendlin]

Thank you for the sample data and the expected result.  I think I understand the request, and will think about a potential approach.  

Why is the Delivery-DateAndTime pegged at noon rather than midnight?

[MMZTM]

Thank you so much for checking out!! I really hope that there is a solution for this.

There is actually no reason for this. I just set it that way.

The only important thing for me would be that the time is within the working hours - so between 8:00 and 16:00.

But it would be no problem, if it is necessary for the calculation to set the time to midnight.

[lbendlin]

Here is the basic approach

Completion-Date = 
var j = SELECTEDVALUE(OPERATION_TABLE[Job-No.])
var mt = max(ORDER_TABLE[Delivery-DateAndTime])
// cumulative minutes from the current step to the end
var m = 0+CALCULATE(sum(OPERATION_TABLE[Time in Minutes]),OPERATION_TABLE[Job-No.]>j)
// series of minutes going back from the delivery date. Large padding ( * 10 ) to cover weekend and non working hour gaps. May need adjustment.
var s = ADDCOLUMNS(GENERATESERIES(0,m*10),"ts",mt-divide([Value],1440))
// filter out rows for weekends and non-working hours
var f = filter(s,WEEKDAY([ts],1) in {2,3,4,5,6} && HOUR([ts])>=8 && HOUR([ts])<16)
// count backwards and cut off
var t = topn(m+1,f,[ts],DESC)
// flip sort order and get first (earliest) row.
var r = topn(1,t,[ts],asc)
// return the timestamp for that  value
return concatenatex(r,[ts]) 

Based on your sample data this results in

As you can see it still needs fine tuning, especially there needs to be a discussion on which minutes to include and which to exclude.  See attached.

[MMZTM]

Dear @lbendlin  ,

Thanks for the links. I hope I have taken all the hints into account now.

I have been looking for a similar discussion on my topic in this forum for quite some time - unfortunately without success so far.

Based on the Delivery-Date of the Order-Table, the Completion-Dates for the respective Job are to be scheduled backward based on the needed Time for every Job. In addition, scheduling should take into account working hours and weekends.

I already have a simple scheduling based on days and without considering working hours and weekends (Code below) - Thanks to @tamerj1 . 

@lbendlinPlease let me know if I have understood the hints in the links correctly and this is the right way to post 🙂

It would be great if someone could help me.

Thanks a lot in advance!

Here is the Sample file in .pbix-format, the Basic-Data as .csv and .xlsx-format.

PowerBI_Community

Also in the following the Relationships:

The expecting Result for the Compleation-Date for Order.No 111 should be:

Order-No	Job-No	Time in Minutes	Completion-Date
111	1	1056	09.08.2023 03:12 PM
111	2	720	11.08.2023 11:12 AM
111	3	287	11.08.2023 03:59 PM
111	4	241	14.08.2023 12:00 AM

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...