Mark "Active" for all rows since the last time a value was 9 or greater by ID

[itsme]

I need to do this as a calculated column and there is no date table - I know we need a date table, but I'm taking over this dataset and will build it properly, until then, I can't add a date table or else many calculations break. We have machine ID numbers and dates that they have been inspected. For each date, we measure the size of the padding on some components on the machine. Everytime the padding increases by 9 inches or more, we know the component has been replaced in the next inspection date. There's a column that identifies if the wear has been 9 inches or more. Machines can have components replaced multiple times. I need to identify the last time the component was replaced - so the inspection after the last time the wear was 9+ inches - and mark each inspection since then as "Active" and the others as "Inactive". I need to do this for each machine ID number.

Below is an example of the data I have so far. The "Status" column is what I need to solve for. Can anyone please help me solve this?

Machine ID	Inspection Date	Component Wear	Status
12345	5/29/2023	2	Active
12345	3/25/2023	5	Active
12345	1/15/2023	2	Active
12345	12/22/2022	10	Inactive
12345	6/5/2022	3	Inactive
54545	4/29/2023	4	Active
54545	2/25/2023	9	Inactive

[NaveenGandhi · Accepted solution]

Hello @itsme 

Try the below Dax.

Status =
VAR Inspection_9 =
CALCULATE (
MAX ( 'Component Active'[Inspection Date] ),
'Component Active'[Component Wear] >= 9,
ALLEXCEPT ( 'Component Active', 'Component Active'[Machine ID] )
)
RETURN
IF (
AND (
'Component Active'[Component Wear] < 9,
'Component Active'[Inspection Date] > Inspection_9
),
"Active",
IF ( 'Component Active'[Inspection Date] <= Inspection_9, "Inactive" )
)

Let me know if you have any questions or issues.

If this post helps, then please consider Accept it as the solution to help the others find it more quickly.