Forum Discussion

Fredde86's avatar
Fredde86
Advocate I
1 year ago

Problem with a Delivery Precision-measure not calculating properly (NETWORKDAYS?!)

I have a measure for the number of networkdays between an Order ID is reported and the Due Date of said Order ID. And when the number of days are decided we then have three different categories,
"On Time"  -1 day, on due date, +1 day

"Late" > 1 day after Due Date

"Early" < -1 day before Due Date

and the measure looks like this:

NetWorkdays = 
SUMX(
    FILTER(
        MANUFACTURING_ORDERS,
        MANUFACTURING_ORDERS[Order nr] = MAX( ITEM_TRANSACTION_HISTORY_SUM[Order nr] ) &&
        MANUFACTURING_ORDERS[Item] = MAX( ITEM_TRANSACTION_HISTORY_SUM[Item] )
    ),
    VAR DueDate = MANUFACTURING_ORDERS[Order Due]
    VAR CompletionDate = MAX( ITEM_TRANSACTION_HISTORY_SUM[Date reported])
    VAR NetworkDays =
        NETWORKDAYS(
            DueDate,
            CompletionDate
        , 1, 'Helgdagar & semester' )
    RETURN
                IF(
            DueDate = CompletionDate,
            0,
            IF(
                DueDate > CompletionDate,
                NetworkDays + 1,
                NetworkDays - 1
            )
        )
)

I then divide the NetWorkdays into the three catergoies mentioned:

Status Order = 
SWITCH( 
    TRUE(), 
        ISBLANK( 
            ITEM_TRANSACTION_HISTORY_SUM[NetWorkdays] ), BLANK() ,
            ITEM_TRANSACTION_HISTORY_SUM[NetWorkdays] <= -2 , "Early" ,
            ITEM_TRANSACTION_HISTORY_SUM[NetWorkdays] >= 2 , "Late" ,
            ITEM_TRANSACTION_HISTORY_SUM[NetWorkdays] = 1 || ITEM_TRANSACTION_HISTORY_SUM[NetWorkdays] = 0 || ITEM_TRANSACTION_HISTORY_SUM[NetWorkdays] = -1 , "On time" )

And lastly I calculate how many of each OrderID are completed in each category:

Late (> +1 dag) = 
CALCULATE( [Qty Complete] ,
FILTER( ITEM_TRANSACTION_HISTORY_SUM ,
        ITEM_TRANSACTION_HISTORY_SUM[Status Order] = "Late" )
       )

Early (< -1 dag) = 
CALCULATE( [Qty Complete] ,
FILTER( ITEM_TRANSACTION_HISTORY_SUM ,
        ITEM_TRANSACTION_HISTORY_SUM[Status Order] = "Early" ) 
)

On Time = 
CALCULATE( [Qty Complete] ,
FILTER( ITEM_TRANSACTION_HISTORY_SUM ,
        ITEM_TRANSACTION_HISTORY_SUM[Status Order] = "On time" )
)

 

Our company doesnt have any production on fridays the first quarter and I have added these fridays to my weekend/holiday-table 'Helgdagar & semester', and the number of networkdays are correct, but it doesnt go in the correct category. Instead of beeing "On time", it says it´s "Early":

 

Grateful for any assistance on this matter ❤️

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Fredde86 ,

    From your description and screenshots, it seems that your measure [Qty Complete] or measure [Early (< -1 dag)] is affected by some context, which causes its calculation result to be affected by some data filtering. What is your DAX of measure [Qty Complete]?
    Could you please share your pbix or your sample data (use test data instead of your actual data)? It is almost impossible to know your problem with DAX alone.

    Best Regards,
    Dino Tao