Calculate Column based on different Values

[zombieug]

Hi forum

I would appreciate your help on the following matter:

down below you can see an example table. I would like to calculate the column Lifecycle based on:

partnumber & the date difference between the applied action install.

I hope the example table explains better what I am trying to do (calculate lifecycle in days --> for each partnumber if install dates are available - lifecycle intervall is last install date subtracted by the the install date before).

I am kind of new to Power BI and DAX so I would really appreciate it if somebody could help me on that matter.

If the example I am showing is not doable in Power BI or you need any further information please let me know.

[Zubair_Muhammad · Accepted solution]

@zombieug

Try this Calculated Column

LifeCycle =
VAR CurrentDate = TableName[Date]
VAR PreviousDate =
    CALCULATE (
        MAX ( TableName[Date] ),
        FILTER (
            ALLEXCEPT ( TableName, TableName[PartNumber] ),
            TableName[Action] = "Install"
                && TableName[Date] < CurrentDate
        )
    )
RETURN
    IF (
        NOT ( ISBLANK ( PreviousDate ) )
            && TableName[Action] = "Install",
        DATEDIFF ( PreviousDate, CurrentDate, DAY )
    )

[zombieug]

@Zubair_Muhammad

Amazing. Thanks for helping out that fast.