Forum Discussion

MMZTM's avatar
MMZTM
Frequent Visitor
3 years ago
Solved

Backwards Scheduling DAX

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 completi...
"DAX"
  • tamerj1's avatar
    tamerj1
    3 years ago

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

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's avatar
    tamerj1
    Community Champion
    3 years ago

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

      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!