Forum Discussion
Backward scheduling based on working time DAX
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...
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
Super User
Super UserHere 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.