Forum Discussion

Fredde86's avatar
Fredde86
Advocate I
1 year ago
Solved

Help with NETWORKDAYS counting today as 1, need 0

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 th...
Need Help
  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    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

     

     

     

Bibiano_Geraldo's avatar
Bibiano_Geraldo
Super User
1 year ago

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's avatar
Fredde86
Advocate I
1 year ago

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 nrDateDue Date *History*NetWorkdays *History*Expected output
MALR7602024-01-102024-01-0822
MALT1502024-01-102024-01-0822
MALV8902024-01-102024-01-0921
MAMC2002024-01-102024-01-0921
MAMF2702024-01-102024-01-1000
MAMF8302024-01-102024-01-1000
MAMS0602024-01-102024-01-11-2-1
MAMT0702024-01-102024-01-11-2-1
MAMV9702024-01-102024-01-11-2-1
MAND5302024-01-102024-01-12-3-2
MAND7002024-01-102024-01-12-3-2

 

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    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's avatar
      Fredde86
      Advocate I
      1 year ago

      Works like a charm, thank you so much!