Lead time calculation based on predecessors

[Anonymous]

Dear community members,

I am trying to find a solution for a lead time calculation which is based on single confirmations in a process.

My table in Power BI looks like this (sorted for order number):

I would like to calculate the lead time between two confirmations (rows).

The calculation itself is only ISDD - ISDD Predecessor.

However, I don't know how to get the predecessor date and time.

The dependency/logic would be for each predecessor cell:

1.  search for the same order number and an activity number which is lower than its own
2. if there is no lower activity number, cell = zero
3. copy the ISDD to the cell

I've tried it in excel before. With sorting it is more less easy, but I don't know how to do it here.

Any ideas how to solve this?

Thanks in advance!

[patoduck · Accepted solution]

Add a column =

ISDD predecessor=
CALCULATE (
    MAX ( YourTableName[ISDD]),
    (
        FILTER (
            YourTableName,
            EARLIER (YourTableName[Order] ) = YourTableName[Order]
                && EARLIER ( YourTableName[ISDD] ) > YourTableName[ISDD]
        )
    )
)

Without the workbook is hard to get it right, but try that first 🙂

Lastly the difference in day you get it like this:
add  column

=DATEDIFF([ISDD predecessor],[ISDD],DAY)