Forum Discussion

P_Crane31's avatar
P_Crane31
Frequent Visitor
3 years ago
Solved

DAX elapsed time calculation misbehaving?

Hello all!  I have two seperate, but related questions on some strange results when I use DAX to calcualte elapsed business days from order palcement to order shipment. Both issues occur on both Des...
Need Help
  • johnt75's avatar
    johnt75
    3 years ago

    I think you can just use the NETWORKDAYS function, e.g.

    business_days_elapsed =
IF (
    NOT ( ISBLANK ( elapsed_time[order_received_in_hw] ) )
        && NOT ( ISBLANK ( elapsed_time[order_last_updated_at] ) ),
    NETWORKDAYS (
        elapsed_time[order_received_in_hw],
        elapsed_time[order_last_updated_at]
    )
)

    You can also provide to the NETWORKDAYS function a list of dates to consider as holidays.

P_Crane31's avatar
P_Crane31
Frequent Visitor
3 years ago

Oops... I just found the DAX formatter...

 

business_days_elapsed =
ROUNDDOWN (
    DATEDIFF (
        elapsed_time[order_received_in_hw],
        elapsed_time[order_last_updated_at],
        DAY
    ) / 7,
    0
) * 5
    + MOD (
        5 + WEEKDAY ( elapsed_time[order_last_updated_at] )
            - WEEKDAY ( elapsed_time[order_received_in_hw] ),
        5
    )