Forum Discussion

eddd83's avatar
eddd83
Resolver I
6 years ago
Solved

conditional calculated column based on previous rows

IDStatusCost

1

Start10
1Apple 
2Start  
2End10
3Banana 
3Carrot 
4Durian 
4End 

 

Need help creating the calculated column "cost".

 

If "status = start", the cost = 10. However, if for the same ID, there is also "status = end", then "cost = 10" only for the "status = end", while cost is blank for "status = start".

 

Any other type of status, results in "cost = (blank)" [ID #3]

Need at least one line to have "status = start" for the cost calculation to work [ID #4].

 

Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    Here is one way to do it (there is probably a shorter way too).  Please try this expression in your calculated column, replacing the Cost table name with your actual one.

     

    New Cost Column =
VAR isstartrow = Cost[Status] = "Start"
VAR isendrow = Cost[Status] = "End"
VAR startrow =
    NOT (
        ISBLANK (
            CALCULATE (
                COUNTROWS ( cost ),
                ALLEXCEPT ( Cost, Cost[ID] ),
                Cost[Status] = "Start"
            )
        )
    )
VAR endrow =
    NOT (
        ISBLANK (
            CALCULATE (
                COUNTROWS ( cost ),
                ALLEXCEPT ( Cost, Cost[ID] ),
                Cost[Status] = "End"
            )
        )
    )
RETURN
    SWITCH (
        TRUE (),
        AND ( startrow, isendrow ), 10,
        AND ( NOT ( endrow ), AND ( isstartrow, startrow ) ), 10,
        BLANK ()
    )

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

1 Reply

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    Here is one way to do it (there is probably a shorter way too).  Please try this expression in your calculated column, replacing the Cost table name with your actual one.

     

    New Cost Column =
VAR isstartrow = Cost[Status] = "Start"
VAR isendrow = Cost[Status] = "End"
VAR startrow =
    NOT (
        ISBLANK (
            CALCULATE (
                COUNTROWS ( cost ),
                ALLEXCEPT ( Cost, Cost[ID] ),
                Cost[Status] = "Start"
            )
        )
    )
VAR endrow =
    NOT (
        ISBLANK (
            CALCULATE (
                COUNTROWS ( cost ),
                ALLEXCEPT ( Cost, Cost[ID] ),
                Cost[Status] = "End"
            )
        )
    )
RETURN
    SWITCH (
        TRUE (),
        AND ( startrow, isendrow ), 10,
        AND ( NOT ( endrow ), AND ( isstartrow, startrow ) ), 10,
        BLANK ()
    )

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat