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, 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

 

"DAX"

5 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago

    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-Forum/ba-p/963216
    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/1447523

  • MMZTM's avatar
    MMZTM
    Frequent Visitor
    3 years ago

    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-NoJob-NoTime in MinutesCompletion-Date
    1111105609.08.2023 03:12 PM
    111272011.08.2023 11:12 AM
    111328711.08.2023 03:59 PM
    111424114.08.2023 12:00 AM
  • 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.