Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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
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
Hi @baadshah
For a calculated column please try
State =
INT (
CALCULATE (
MIN ( 'Table'[CreatedDate] ),
ALLEXCEPT (
'Table',
'Table'[ID]
)
) = 'Table'[CreatedDate]
) * 2
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
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?
@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 🙂
Flag =
INT (
CALCULATE (
MIN ( 'Table'[CreatedDate] ),
ALLEXCEPT ( 'Table', 'Table'[ID] ),
'Table'[State] = 2
) = 'Table'[CreatedDate]
) * 2
User | Count |
---|---|
59 | |
22 | |
18 | |
18 | |
16 |
User | Count |
---|---|
87 | |
59 | |
52 | |
37 | |
21 |