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 following activities are Waiting, then we record FLAGGED, as in the image below.

Note that there are multiple transportation methods in our database with transportation ID. Please help on how to achieve the calculation in BI. Thanks.

 

 

Transportation IDDateFromToDurationTransportationShortLocShortLoc InitialDetailedLocActivity Expected Column
340501 Mar 2200:0012:400,52777778VolvoFacility FChemical Plant AOn Premise 
340601 Mar 2212:4014:450,08680556VolvoFacility FChemical Plant AOn Premise 
340701 Mar 2214:4523:590,38472222VolvoFacility FChemical Plant AOn Premise 
340802 Mar 2200:0006:300,27083333VolvoFacility FChemical Plant AOn Premise 
340902 Mar 2206:3007:450,05208333VolvoEnrouteE Enroute 
341002 Mar 2207:4508:250,02777778VolvoEnrouteE Enroute 
341102 Mar 2208:2509:500,05902778VolvoLoading PortPNorleans PortWaitingJIT - Flagged
341202 Mar 2209:5011:550,08680556VolvoLoading PortPNorleans PortWaitingJIT - Flagged
341302 Mar 2211:5516:550,20833333VolvoLoading PortPNorleans PortIn harbour 
341402 Mar 2216:5517:400,03125VolvoLoading PortPNorleans PortIn harbour 
341502 Mar 2217:4019:060,05972222VolvoLoading PortPNorleans PortIn harbour 
341602 Mar 2219:0619:500,03055556VolvoLoading PortPNorleans PortIn harbour 
341702 Mar 2219:5023:590,17291667VolvoEnrouteE Enroute 
341803 Mar 2200:0003:100,13194444VolvoEnrouteE Enroute 
341903 Mar 2203:1003:350,01736111VolvoFacility FChemical Plant CWaitingJIT - Flagged
342003 Mar 2203:3505:300,07986111VolvoFacility FChemical Plant CWaitingJIT - Flagged
342103 Mar 2205:3006:000,02083333ToyotaFacility FChemical Plant COn Premise 
342203 Mar 2206:0007:050,04513889ToyotaFacility FChemical Plant COn Premise 
  • 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

3 Replies

  • 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