Forum Discussion

Fredde86's avatar
Fredde86
Advocate I
1 year ago
Solved

NETWORKDAYS between two tables containing duplicate OrderIDs

I have a DATEDIFF calculated column that gives me the datediff between two tables and it has two flaws that I would like to solve with a NETWORKDAYS solution, if possible..

 

Diff Days *History* = 
    DATEDIFF (
            MAXX ( RELATEDTABLE ( MANUFACTURING_ORDERS_HISTORY ), MANUFACTURING_ORDERS_HISTORY[Due Date *History*] ),
            ITEM_TRANSACTION_HISTORY_SUM[Date],
            DAY
)

 

 

In my MANUFACTURING_ORDER_HISTORY there are duplicate OrderIDs, since we only have a set numer of possible OrderIDs. I.E OrderIDs will turn up again in 2-3 years and be put in the _HISTORY table. 


In order to visualize each unique Order I'm combining OrderID with "Item Nr".

Problem 1: I need a NETWORKDAYS instead of Datediff

Problem 2:  The inclusion of MAXX  in the DATEDIFF-column makes it so that the datediff does not evaluate all possible dates, rather than only the highest. I would prefer all dates in my [Due Date *History*] to be evaluated, row by row. Exchanging MAXX with ALL didn´t work though for example.
See below picture for my problem, highlighted a row where the OrderID is the same but the Item Nr isn´t = shouldn´t be evaluated preferably (?) in yellow and where it should, in green.

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi, Fredde86 
    Based on your information, I created sample tables:

    Then create a new measure, try the following DAX expression:

    Diff Days *History* = 
    SUMX (
        FILTER (
            MANUFACTURING_ORDERS_HISTORY,
            MANUFACTURING_ORDERS_HISTORY[OrderID] = MAX(ITEM_TRANSACTION_HISTORY_SUM[OrderID]) &&
            MANUFACTURING_ORDERS_HISTORY[Item Nr] = MAX(ITEM_TRANSACTION_HISTORY_SUM[Item Nr])
        ),
        NETWORKDAYS (
            MANUFACTURING_ORDERS_HISTORY[Due Date History],
            MAX(ITEM_TRANSACTION_HISTORY_SUM[Date])
        )
    )

    Put it in table visual, here is my preview:

     

    How to Get Your Question Answered Quickly

    Best Regards

    Yongkang Hua

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, Fredde86 
    Based on your information, I created sample tables:

    Then create a new measure, try the following DAX expression:

    Diff Days *History* = 
    SUMX (
        FILTER (
            MANUFACTURING_ORDERS_HISTORY,
            MANUFACTURING_ORDERS_HISTORY[OrderID] = MAX(ITEM_TRANSACTION_HISTORY_SUM[OrderID]) &&
            MANUFACTURING_ORDERS_HISTORY[Item Nr] = MAX(ITEM_TRANSACTION_HISTORY_SUM[Item Nr])
        ),
        NETWORKDAYS (
            MANUFACTURING_ORDERS_HISTORY[Due Date History],
            MAX(ITEM_TRANSACTION_HISTORY_SUM[Date])
        )
    )

    Put it in table visual, here is my preview:

     

    How to Get Your Question Answered Quickly

    Best Regards

    Yongkang Hua

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Fredde86's avatar
    Fredde86
    Advocate I
    1 year ago

    Hi, Anonymous 
    Thank you for your answer!

    However, I don´t get any values when I create your measure and put it into a table. 
    Could it have something to do with my relationships beeing many-to-many between these tables? (OrderId-OrderId)
    Thankful for any assistance!

     




    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi, Fredde86 

      I didn't give them a relationship.

       

      When I try to give them a many-to-many relationship, I also get a blank.

       

      You may be able to try deleting the relationship

       

      Best Regards

      Yongkang Hua