Conditional calculation based on previous record

[Anonymous]

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 ID	Date	From	To	Duration	Transportation	ShortLoc	ShortLoc Initial	DetailedLoc	Activity	Expected Column
3405	01 Mar 22	00:00	12:40	0,52777778	Volvo	Facility	F	Chemical Plant A	On Premise
3406	01 Mar 22	12:40	14:45	0,08680556	Volvo	Facility	F	Chemical Plant A	On Premise
3407	01 Mar 22	14:45	23:59	0,38472222	Volvo	Facility	F	Chemical Plant A	On Premise
3408	02 Mar 22	00:00	06:30	0,27083333	Volvo	Facility	F	Chemical Plant A	On Premise
3409	02 Mar 22	06:30	07:45	0,05208333	Volvo	Enroute	E		Enroute
3410	02 Mar 22	07:45	08:25	0,02777778	Volvo	Enroute	E		Enroute
3411	02 Mar 22	08:25	09:50	0,05902778	Volvo	Loading Port	P	Norleans Port	Waiting	JIT - Flagged
3412	02 Mar 22	09:50	11:55	0,08680556	Volvo	Loading Port	P	Norleans Port	Waiting	JIT - Flagged
3413	02 Mar 22	11:55	16:55	0,20833333	Volvo	Loading Port	P	Norleans Port	In harbour
3414	02 Mar 22	16:55	17:40	0,03125	Volvo	Loading Port	P	Norleans Port	In harbour
3415	02 Mar 22	17:40	19:06	0,05972222	Volvo	Loading Port	P	Norleans Port	In harbour
3416	02 Mar 22	19:06	19:50	0,03055556	Volvo	Loading Port	P	Norleans Port	In harbour
3417	02 Mar 22	19:50	23:59	0,17291667	Volvo	Enroute	E		Enroute
3418	03 Mar 22	00:00	03:10	0,13194444	Volvo	Enroute	E		Enroute
3419	03 Mar 22	03:10	03:35	0,01736111	Volvo	Facility	F	Chemical Plant C	Waiting	JIT - Flagged
3420	03 Mar 22	03:35	05:30	0,07986111	Volvo	Facility	F	Chemical Plant C	Waiting	JIT - Flagged
3421	03 Mar 22	05:30	06:00	0,02083333	Toyota	Facility	F	Chemical Plant C	On Premise
3422	03 Mar 22	06:00	07:05	0,04513889	Toyota	Facility	F	Chemical Plant C	On Premise

[v-cazheng-msft]

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]

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 · Accepted solution]

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