In this blog you will understand “How to Identify New vs. Returning Customers based on their Transactions history in Power BI?”
In the Transactions table, we have details of customer transactions. Based on the transaction date, we will identify whether a customer is New or Returning.
Dataset : Transactions
| TransactionID | Date | CustomerID | Revenue |
| 1 | 2023-01-01 | 101 | 500 |
| 2 | 2023-02-01 | 102 | 800 |
| 3 | 2023-03-01 | 101 | 600 |
| 4 | 2023-04-01 | 103 | 700 |
| 5 | 2023-05-01 | 104 | 400 |
| 6 | 2023-06-01 | 102 | 300 |
Let’s get started!
Step-1: Load the dataset into Power BI Desktop.
Step-2: Add a calculated column for the Customer Type by right-clicking on the Transaction table and selecting.
Step-3: Now, write the DAX code below.
Customer Type = IF( CALCULATE( COUNTROWS(Transactions), FILTER( ALL(Transactions), Transactions[CustomerID] = EARLIER(Transactions[CustomerID]) && Transactions[Date] < EARLIER(Transactions[Date]) ) ) > 0, "Returning", "New" )
Explanation of the DAX Code:
- The formula checks whether the customer has any prior transactions in the dataset.
EARLIER(Transactions[Date])references the current row’s date to compare it with earlier transactions for the same customer.- If a prior transaction exists, the customer is classified as “Returning”, otherwise, they are classified as “New”.
I hope you enjoyed the post. Your valuable feedback, questions, or comments about this post are always welcome.
