Forum Discussion

MMZTM's avatar
MMZTM
Frequent Visitor
3 years ago

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...
"DAX"
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
3 years ago

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's avatar
MMZTM
Frequent Visitor
3 years ago

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's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago

    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.