Identify records where the first created date meets a condition from a second variable

[baadshah]

i have CRM data with where i need to create a new column which will index a '1' for all ID's where the first invoice issued 'CreatedDate' was paid ('State' = 2). So with the below example only row 2 is deemed as first invoice paid. 

Data context - Each unique 'ID' will have a unique 'ContractID' but many 'InvoiceID'. 

I've tried to nest CALCULATE(MIN(Table[CreatedDate) with RETURN but couldnt get it to work. 

Row	ID	ContractID	InvoiceID	CreatedDate	ModifiedDate	State
1	b6910000-00000-ed11-00000-0001000	CONTRACT-0100	BAGG31322	20/04/2023	20/04/2023	0
2			BAG312683	21/02/2023	23/05/2023	2
3			BAG314743	09/05/2023	09/05/2023	0
4			BAG315613	09/03/2023	09/03/2023	0

[baadshah · Accepted solution]

Hi @tamerj1 the above solution didnt work however i eventually got it to work in two steps.

1st - Identify the earliest date for each ID

Min_Date = CALCULATE(MIN(table[CreatedDate]),ALLEXCEPT(table,table[ID]))

2nd - Create another column to index with 1 for each row that meets Min_Date and state = 2

Commission_Pay = IF(table[State]=2 && table[CreatedDate] = table[Min_Date],1,0)

[tamerj1]

Hi @baadshah 
For a calculated column please try

State =
INT (
    CALCULATE (
        MIN ( 'Table'[CreatedDate] ),
        ALLEXCEPT (
            'Table',
            'Table'[ID]
        )
    ) = 'Table'[CreatedDate]
) * 2

[baadshah]

Hi @tamerj1 , this works for majority of my records but i have a handfull of 'State' = 0 appearing as "Paid" when 0 means Invoice wasnt paid. Is there anyway to tweak this measure to include the 'State'  column has to be 2 and cannot include records with 0

[tamerj1]

@baadshah 

I don't fully understand. Do you mean you have a column that flags paid invoices? Are we talking about a calculated column or a measure?

[baadshah]

@tamerj1 

'State' column = 2 indictates Paid and 0 indicates Unpaid. So effectively i'm looking for records by 'ID' that have the earlist/first 'CreatedDate'  and the 'State' column must be 2. So a nested calculation that will index 1 for each 'ID' records where the first 'CreatedDate' has a 'State' code = 2.

In the dummy data above row #2 meet that criteria because the min date is 21/02/2023 AND the state code = 2. Even if row #1 #3 and #4 had a state code = 2 it would still index 1 on the newly created column as row #2 as that has the min or earliest date. Hope that makes sense 🙂  

[tamerj1]

@baadshah 

Flag =
INT (
CALCULATE (
MIN ( 'Table'[CreatedDate] ),
ALLEXCEPT ( 'Table', 'Table'[ID] ),
'Table'[State] = 2
) = 'Table'[CreatedDate]
) * 2