Calculated column to find lowest date with a filter on status value per ordernumber

[nijos]

I have this table with date, status and ordernumber.

Date	Status	OrderNumber
01/01/2024	10	1000
01/02/2024	20	1000
01/03/2024	30	1000
01/04/2024	30	1000
01/05/2024	40	1000
01/06/2024	50	1000
01/01/2025	10	1100
01/02/2025	20	1100
01/06/2025	30	1100
01/04/2025	30	1100
01/05/2025	30	1100
01/06/2025	50	1100

For each OrderNumber, I want to find the first date that is on a row with status 30.

In a calculated column, that row must get a 1, the other rows should get a 0.

Date	Status	OrderNumber	Calculated Column
01/01/2024	10	1000	0
01/02/2024	20	1000	0
01/03/2024	30	1000	1
01/04/2024	30	1000	0
01/05/2024	40	1000	0
01/06/2024	50	1000	0
01/01/2025	10	1100	0
01/02/2025	20	1100	0
01/06/2025	30	1100	0
01/04/2025	30	1100	1
01/05/2025	30	1100	0
01/06/2025	50	1100	0

Thank you for your help on this.

[nijos · Accepted solution]

Calculated Column =

VAR CurrentOrder = 'Table'[OrderNumber]

VAR FilteredTable =

    FILTER ( 'Table', 'Table'[Status] = 30 && 'Table'[OrderNumber] = CurrentOrder )

VAR Result =

    CALCULATE ( MIN ( 'Table'[Date] ), FilteredTable )

RETURN

    IF ( Result = 'Table'[Date], 1, 0 )