Calculate days in current process step

Hi tblackburn,

Please create calculated columns using below DAX formulas in source table (in my test, it's named as 'Work Order').

New Col1 =
VAR lastdaypull =
    CALCULATE (
        MAX ( 'Work Order'[Day Pulled] ),
        ALLEXCEPT ( 'Work Order', 'Work Order'[Work Order] ),
        'Work Order'[Day Pulled] < EARLIER ( 'Work Order'[Day Pulled] )
    )
RETURN
    IF (
        CALCULATE (
            LASTNONBLANK ( 'Work Order'[Current Process Step], 1 ),
            FILTER (
                ALLEXCEPT ( 'Work Order', 'Work Order'[Work Order] ),
                'Work Order'[Day Pulled] = lastdaypull
            )
        )
            <> 'Work Order'[Current Process Step],
        1,
        0
    )


New Col2 =
CALCULATE (
    SUM ( 'Work Order'[New Col1] ),
    FILTER (
        ALLEXCEPT ( 'Work Order', 'Work Order'[Work Order] ),
        'Work Order'[Day Pulled] <= EARLIER ( 'Work Order'[Day Pulled] )
    )
)


Days in Current Process Step =
RANKX (
    FILTER (
        'Work Order',
        'Work Order'[Work Order] = EARLIER ( 'Work Order'[Work Order] )
            && 'Work Order'[New Col2] = EARLIER ( 'Work Order'[New Col2] )
    ),
    'Work Order'[Day Pulled],
    ,
    ASC,
    DENSE
)

As resource data is automatically appended every morning, each time you open this report, please refresh report page manually, the table view will be updated to latest data.

Regards,

Yuliana Gu