Forum Discussion

zombieug's avatar
zombieug
Icon for Helper I rankHelper 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 & 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.:smileyvery-happy:

 

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 )
    )

2 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity 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 )
    )