Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Lead time calculation based on predecessors

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!

Need Help
  • patoduck's avatar
    patoduck
    7 years ago

    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)

1 Reply

  • patoduck's avatar
    patoduck
    Helper III
    7 years ago

    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)