Forum Discussion

zombieug's avatar
zombieug
Helper I
8 years ago
Solved

Calculate Column based on different Values

  Hi forum :smileyhappy:   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 & th...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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 )
    )
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

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's avatar
    zombieug
    Helper I
    8 years ago

    Zubair_Muhammad

     

    Amazing. Thanks for helping out that fast.