Help with NETWORKDAYS counting today as 1, need 0

[Fredde86]

Hi,

I would like my NETWORKDAYS measure to show 0 if the dates are the same, example below.

Simply adding "- 1" to my measure in the end does not work since it messes up when I have blank rows that I don´t want to include in the calculation.

My measure (credit to @Anonymous) : 

NetWorkdays *History* = 
    SUMX (
        FILTER (
            MANUFACTURING_ORDERS_HISTORY,
            MANUFACTURING_ORDERS_HISTORY[Order nr (HISTORY)] = MAX(ITEM_TRANSACTION_HISTORY_SUM[Order nr]) &&
            MANUFACTURING_ORDERS_HISTORY[Finish_Item_Number] = MAX(ITEM_TRANSACTION_HISTORY_SUM[Item])
        ),
        NETWORKDAYS (
            MANUFACTURING_ORDERS_HISTORY[Due Date *History*],
            MAX(ITEM_TRANSACTION_HISTORY_SUM[Date])
        )
    )

The result below and what I want to show in the last column:

OrderID	Date (Order completed)	Due Date *History*	NetWorkdays *History*	What I want
1001	2024-06-08	2024-06-08	1	0
1002	2024-06-09	2024-06-08	2	1

Thanks in advance!

[Bibiano_Geraldo]

Hi @Fredde86 , 

Please try the bellow measure:

NetWorkdays *History* = 
SUMX(
    FILTER(
        MANUFACTURING_ORDERS_HISTORY,
        MANUFACTURING_ORDERS_HISTORY[Order nr (HISTORY)] = MAX(ITEM_TRANSACTION_HISTORY_SUM[Order nr]) &&
        MANUFACTURING_ORDERS_HISTORY[Finish_Item_Number] = MAX(ITEM_TRANSACTION_HISTORY_SUM[Item])
    ),
    IF(
        MANUFACTURING_ORDERS_HISTORY[Due Date *History*] = MAX(ITEM_TRANSACTION_HISTORY_SUM[Date]),
        0,
        NETWORKDAYS(
            MANUFACTURING_ORDERS_HISTORY[Due Date *History*],
            MAX(ITEM_TRANSACTION_HISTORY_SUM[Date])
        )
    )
)

Let me know if it works, if no, please provide the sample data with no sensitive data

[Fredde86]

Hi @Bibiano_Geraldo ,
Thanks for your reply.

Added your configuration to my measure and it solved the problem I was having with same date = 0.
However there are some calculations that doesn´t return desired values, look below:

Order nr	Date	Due Date *History*	NetWorkdays *History*	Expected output
MALR760	2024-01-10	2024-01-08	2	2
MALT150	2024-01-10	2024-01-08	2	2
MALV890	2024-01-10	2024-01-09	2	1
MAMC200	2024-01-10	2024-01-09	2	1
MAMF270	2024-01-10	2024-01-10	0	0
MAMF830	2024-01-10	2024-01-10	0	0
MAMS060	2024-01-10	2024-01-11	-2	-1
MAMT070	2024-01-10	2024-01-11	-2	-1
MAMV970	2024-01-10	2024-01-11	-2	-1
MAND530	2024-01-10	2024-01-12	-3	-2
MAND700	2024-01-10	2024-01-12	-3	-2

[Bibiano_Geraldo · Accepted solution]

Hi @Fredde86 ,

Please try the bellow DAX measure:

NetWorkdays *History* = 
SUMX(
    FILTER(
        MANUFACTURING_ORDERS_HISTORY,
        MANUFACTURING_ORDERS_HISTORY[Order nr (HISTORY)] = MAX(ITEM_TRANSACTION_HISTORY_SUM[Order nr]) &&
        MANUFACTURING_ORDERS_HISTORY[Finish_Item_Number] = MAX(ITEM_TRANSACTION_HISTORY_SUM[Item])
    ),
    VAR DueDate = MANUFACTURING_ORDERS_HISTORY[Due Date *History*]
    VAR CompletionDate = MAX(ITEM_TRANSACTION_HISTORY_SUM[Date])
    VAR NetworkDays =
        NETWORKDAYS(
            DueDate,
            CompletionDate
        )
    RETURN
        -- Adjust result to match expectations
        IF(
            DueDate = CompletionDate,
            0,
            IF(
                DueDate > CompletionDate,
                NetworkDays - 1,
                NetworkDays + 1
            )
        )
)

Please, let me know if you're facing any issue.

If this updated measure not works, please provide me a sample data with no sensitive information for your table: MANUFACTURING_ORDERS_HISTORY and ITEM_TRANSACTION_HISTORY_SUM

[Fredde86]

Works like a charm, thank you so much! 

[Bibiano_Geraldo]

Happy it works @Fredde86 

[Ritaf1983]

Hi @Fredde86 
just add -1 
in the end of the formula , something like :

NetWorkdays *History* =
SUMX (
FILTER (
MANUFACTURING_ORDERS_HISTORY,
MANUFACTURING_ORDERS_HISTORY[Order nr (HISTORY)] = MAX(ITEM_TRANSACTION_HISTORY_SUM[Order nr]) &&
MANUFACTURING_ORDERS_HISTORY[Finish_Item_Number] = MAX(ITEM_TRANSACTION_HISTORY_SUM[Item])
),
NETWORKDAYS (
MANUFACTURING_ORDERS_HISTORY[Due Date *History*],
MAX(ITEM_TRANSACTION_HISTORY_SUM[Date])-1
)
)

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