Forum Discussion

Fredde86's avatar
Fredde86
Icon for Advocate I rankAdvocate 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
Icon for Super User rankSuper 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
    Icon for Advocate I rankAdvocate 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
      Icon for Super User rankSuper 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
        Icon for Advocate I rankAdvocate I
        1 year ago

        Works like a charm, thank you so much!