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 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:

 
OrderIDDate (Order completed)Due Date *History*NetWorkdays *History*What I want
10012024-06-082024-06-0810
10022024-06-092024-06-0821

 

Thanks in advance!

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

     

     

     

6 Replies

  • 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

         

         

         

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    1 year ago

    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.