Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Conditional calculation based on previous record

Hi all, Here is a situation I am faced with. I am trying to flag out certain situations where our vehicles arrive and kept on standby. The rule is that when the previous row is Enroute and the fol...
  • v-cazheng-msft's avatar
    v-cazheng-msft
    4 years ago

    Hi Anonymous ,

     

    Please try this Calculated column.

    Flagged_CalculatedColumn =
 VAR Count_ =
    CALCULATE (
        COUNT ( 'Table'[Transportation ID] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Transportation] ),
            'Table'[Activity ] = "Enroute"
                && EARLIER('Table'[Activity ])  = "Waiting"
        )
    )
RETURN
    IF ( ISBLANK ( Count_ ), "", "  JIT - Flagged" )

     

    Then, the visual should look like this.

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

     

    Best Regards,

    Community Support Team _ Caiyun

v-cazheng-msft's avatar
v-cazheng-msft
Community Support
4 years ago

Hi Anonymous ,

 

Not very clear about the calculation logic, but you may try this Measure.

Flagged =
VAR Count_ =
    CALCULATE (
        COUNT ( 'Table'[Transportation ID] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Transportation] ),
            'Table'[Activity ] = "Enroute"
                && MAX ( 'Table'[Activity ] ) = "Waiting"
        )
    )
RETURN
    IF ( ISBLANK ( Count_ ), "", "  JIT - Flagged" )

 

Then, the result should look like this.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know whether the result will be impacted by transportation methods or transportation ID . Thanks a lot!

 

Best Regards,

Community Support Team _ Caiyun

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi v-cazheng-msft thanks for your help. Do you think I can implement this as a column? rather than measure? Thanks again.

    • v-cazheng-msft's avatar
      v-cazheng-msft
      Community Support
      4 years ago

      Hi Anonymous ,

       

      Please try this Calculated column.

      Flagged_CalculatedColumn =
 VAR Count_ =
    CALCULATE (
        COUNT ( 'Table'[Transportation ID] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Transportation] ),
            'Table'[Activity ] = "Enroute"
                && EARLIER('Table'[Activity ])  = "Waiting"
        )
    )
RETURN
    IF ( ISBLANK ( Count_ ), "", "  JIT - Flagged" )

       

      Then, the visual should look like this.

       

      If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

       

      Best Regards,

      Community Support Team _ Caiyun