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 completion dates for the respective operations are to be scheduled backwards.
Would be great if someone could help me.
Thanks a lot in advance!

 

 

"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

4 Replies

  • 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!

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

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