Hi Team,
I have an orders table in Power BI. It contains columns: customer_id, order_id (unique for every order), time_pickup_date, total_sales (per order_id), order_status (per order_id), and _order rank (sequence of orders per customer according to time_pickup_date).
I need to create a table visual where: The first column is the time_pickup_date, the second column is the total count (distinct customer_id) of all first-time customers, and the third column is the total count (distinct customer_id) of all returning customers. My definition of a first-time customer is if the total distinct order_id is 1 or max order is 1 while returning is 2 or more order_id.
I have a customer table linked to the orders table via a one-to-many relationship using the customer_id key.
I created a calculated column in my customer's table where if the total order is 1, "first time customer" while "returning" if 2 or more. then, I created a table visual and was filtering it by the time_pickup_date. Here is the issue:
for example:
The customers first order was on April 1, 2022 by that time, he is a first time customer
Then, on April 23, 2022, he made another order making him a returning customer. The issue is when I go back to April 1, 2022 instead of showing "first time customer", he is not a returning customer because in the customers table, his total orders is now 2.
I need help. It should go back to first time customer since I went back to April 1, 2022
