NETWORKDAYS between two tables containing duplicate OrderIDs

[Fredde86]

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.

[Fredde86]

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]

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

[Anonymous · Accepted solution]

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.