Calculated column customer bought that product for the first time

[Juan_Quikin5]

Hey guys,

I have a calendar table and a table with four columns: date, customer_id, product_id, and sales_amount.

I would like a calculated column showing 1 if that customer buys that product for the first time and 0 otherwise.

Any idea? Thanks guys!

[ichavarria]

Hi @Juan_Quikin5,

You can use a combination of functions such as GROUPBY, MINX and IF to create a calculated column that shows 1 if a customer buys a product for the first time and 0 otherwise. Here's an example formula you might use:

=IF(MINX(GROUPBY('Sales', [Customer_id], [Product_id], "MinDate", MIN('Sales'[Date])),'Sales'[Date])=[Date],1,0)

This formula groups the 'Sales' table by 'Customer_id' and 'Product_id', and then finds the minimum date for each group using the MIN function. It then compares this minimum date to the date in the current row of the 'Sales' table. If they match, it returns 1, indicating that this is the first purchase for the customer and product combination, otherwise it returns 0.

Note that this formula assumes that the 'Sales' table is named "Sales" and that the columns in the formula ('Date', 'Customer_id', 'Product_id', and 'Sales_amount') have the same names as in your table. You may need to adjust the formula accordingly if your column names are different.

Also note that this formula creates a calculated column and not a measure, so it will be computed for each row in the table. If you have a large number of rows, this may impact performance.

Best regards, 

Isaac Chavarria

If this post helps, then please consider Accepting it as the solution and give Kudos to help the other members find it more quickly

[Juan_Quikin5 · Accepted solution]

It didn't work out for me, all values in the column were 1. However, you gave me a great idea! I created a table with the "groupby" part of your formula. Then, formatted the "mindate" column to have the same format as the sales table and added a column with value 1. And finally, I used the "lookupvalue" function to "merge" those two tables and it worked! Thanks @ichavarria !

[ichavarria]

Glad to hear that it, at least, routed to finding the right solution for you! 

Best regards, 

Isaac Chavarria

If this post helps, then please consider Accepting it as the solution and give Kudos to help the other members find it more quickly