Calculated Field to evaluate if transaction was the first made by user

[Anonymous]

Hi

I have a table which contains transaction-ID, transaction-date and user-id.

I need to be add an additional field which evaluates if a respective transaction was the first made by the user or not.

Screenshot below. Column H (yellow marked) is essentially the desired result. In this example user 1428 first transaction was made on September 7 2017, and is therefore to be marked as "first transaction". All following transactions made by this user are "Further transactions".

Thanks in advance!

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can try creating a calculated column. Basiclly it's to find out the earliest date group by user id.

Column = VAR _MINDATE= CALCULATE(MIN('Table'[DATE]),FILTER('Table',[UserID]=EARLIER('Table'[UserID])))
RETURN IF([DATE]=_MINDATE,"First transactions","Further transactions")

Best Regards,

Stephen Tao

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

[devesh_gupta]

@Anonymous 

• In Power Query Editor, select the table containing transaction data.

• Go to the "Home" tab and click on "Advanced Editor".

• Replace the existing code with the following M code:

let
    Source = YourTableNameHere, // Replace with your actual table name
    GroupedTable = Table.Group(Source, {"user-id"}, {{"MinDate", each List.Min([transaction-date]), type datetime}}),
    MergedTable = Table.Join(Source, "user-id", GroupedTable, "user-id"),
    AddFirstTransactionColumn = Table.AddColumn(MergedTable, "First Transaction", each if [transaction-date] = [MinDate] then "First Transaction" else "Further Transactions")
in
    AddFirstTransactionColumn

• Make sure to replace YourTableNameHere with the actual name of your table.
• Click "OK" to apply the changes.

Now, you should have an additional column named "First Transaction" that indicates whether a transaction is the first made by the user or not.

If you find this insightful, please provide a Kudo and accept this as a solution.

[devesh_gupta]

@Anonymous As you kudoed this reply, if it works for you then can you mark it as a solution so that other users can find it more easily. Thanks.