Find rows before and after matching a condition

Step 1: Order the Table by CompletedDate
First, you need to order the table by CompletedDate within each Code group.

Step 2: Create Tables for Before and After "C"
You can use DAX to create two new tables: one for rows before "ProductDetail" = "C" and one for rows after "ProductDetail" = "C".

Here's how you can do it:

Table for Rows Before "C"
BeforeC =
FILTER(
Tbl,
Tbl[CompletedDate] < CALCULATE(MIN(Tbl[CompletedDate]), Tbl[ProductDetail] = "C", ALLEXCEPT(Tbl, Tbl[Code]))
)
Table for Rows After "C"
AfterC =
FILTER(
Tbl,
Tbl[CompletedDate] >= CALCULATE(MIN(Tbl[CompletedDate]), Tbl[ProductDetail] = "C", ALLEXCEPT(Tbl, Tbl[Code]))
)
Explanation
ALLEXCEPT(Tbl, Tbl[Code]): This function removes all filters from the table except for the Code column, ensuring that the calculation is done within each Code group.
CALCULATE(MIN(Tbl[CompletedDate]), Tbl[ProductDetail] = "C", ALLEXCEPT(Tbl, Tbl[Code])): This calculates the minimum CompletedDate where ProductDetail is "C" within each Code group.
Example
Given your table, the BeforeC table will include all rows with CompletedDate before the first occurrence of "C" within each Code group, and the AfterC table will include all rows with CompletedDate from the first occurrence of "C" onwards.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

We'll modify the DAX code to include ranking and then filter based on that rank.

Step 1: Add Ranking
First, let's create a calculated column to rank the rows within each Code group by CompletedDate.

Tbl =
ADDCOLUMNS(
Tbl,
"Rank", RANKX(
FILTER(Tbl, Tbl[Code] = EARLIER(Tbl[Code])),
Tbl[CompletedDate],
,
ASC
)
)
Step 2: Create Tables for Before and After "C"
Now, we can use this rank to filter the rows for the BeforeC and AfterC tables.

Table for Rows Before "C"
BeforeC =
FILTER(
Tbl,
Tbl[Rank] <= CALCULATE(MIN(Tbl[Rank]), Tbl[ProductDetail] = "C", ALLEXCEPT(Tbl, Tbl[Code]))
)
Table for Rows After "C"
AfterC =
FILTER(
Tbl,
Tbl[Rank] >= CALCULATE(MIN(Tbl[Rank]), Tbl[ProductDetail] = "C", ALLEXCEPT(Tbl, Tbl[Code]))
)
Explanation
ADDCOLUMNS: Adds a new column "Rank" to the table.
RANKX: Ranks the rows within each Code group by CompletedDate in ascending order.
EARLIER: Refers to the current row context within the RANKX function.
FILTER: Filters the rows based on the rank.
This should ensure that your tables are sorted by CompletedDate within each Code group and correctly split into before and after "C".

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!