Backwards Scheduling DAX

[MMZTM]

Hey guys,

I am looking for a new calculated Column by DAX that will create a backward scheduling for me using the two tables shown below.
Based on the delivery date of the order table, the completion dates for the respective operations are to be scheduled backwards.
Would be great if someone could help me.
Thanks a lot in advance!

 

[MMZTM]

Hi @tamerj1 

yes for sure., sorry.

For the Order-No 111: Delivery-Date is 11th August

Job-No 4: Have to be done on 11.08.2023

Job-No 3: Have to be done on 10.08.2023 (--> Because i need 1 Day for Job-No 4)

Job-No 2: Have to be done on 08.08.2023 (--> Because i need 2 Days for Job-No 3)

Job-No 1: Have to be done on 05.08.2023 (--> Because i need 3 Days for Job-No 2)

Please let me know if my question is clearly.

[tamerj1 · Accepted solution]

@MMZTM 
Apologies for thr late reply. I was too busy today.

Completion Date = 
VAR CurrentOrderTable = 
    CALCULATETABLE ( Table1, ALLEXCEPT ( Table1, Table1[Order.No] ) )
VAR TableAfter =
    FILTER ( CurrentOrderTable, Table1[Job-No.] > EARLIER ( Table1[Job-No.] ) )
VAR NumberOfDays = 
    SUMX ( TableAfter, Table1[Time] )
VAR Delivery = RELATED ( Table2[Delivery] )
RETURN
    Delivery - NumberOfDays

[MMZTM]

Hello @tamerj1 
thank you first of all for the quick reply! It works great! I really hope that I can ask another question based on this.

I have replaced the values as minutes for the "Time" column. I also created a calendar table.

Is it possible to calculate the backward scheduling to include the working days and also the weekend based on the Time in Minutes?

 

As an example for order no. 111:
Job-No. 3: Completion-Date 11.08.2023 15:59 PM --> 14.08.2023 12:00 PM minus 0,52083 Days

=11.08.2023 15:59 PM (because of the Weekend)

Job-No. 2: Completion-Date 10.08.2023 11:59 AM --> 11.08.2023 15:59 PM minus 1,5 Days 

= 10.08.2023 11:59 AM

Job-No. 1: Compleation-Date 08.08.2023 10:23 AM --> 10.08.2023 11:59 PM minus 2,2 Days

= 08.08.2023 10:23 AM

 

I hope I have explained it understandably.

Thank you very much in advance for your help!

[tamerj1]

Hi @MMZTM 
Please clarify what is the xpected result using one example.