New and repeating customers

[lridicki]

Hi,

I need help how to get two types of data in PB

Data I have in a table: Customer name, customer ID, Date of purchase, Year

1. A need to se who is new customer (is only once in a table and is in current year) and who is repeating customer (is multiple times in a table or is in a current and previous year) all earlier then this year are Old if they are single time in table and repeating if multiple times.
2. This I do not know is it possible to get a data by year. So, in example if I select 2023, I see new customers in this year, and all other are or repeating or old, but if I select 2022, I see new in 2022 and old or repeating for previous years.

[Adamboer]

To achieve this, you can create a new column in your table using Power Query Editor that categorizes each customer based on their purchase history. Here's a possible approach:

1. Open Power Query Editor by selecting "Transform data" in the Home tab of Power BI Desktop.
2. Select your table, go to the "Add Column" tab and select "Custom Column".
3. In the formula bar, enter the following formula:
   
   = if [Year] = Date.Year(DateTime.LocalNow()) and
   List.Count(Table.SelectRows(#"Previous Steps", each [Customer ID] = [#"Customer ID"])) = 0 then "New"
   else if List.Count(Table.SelectRows(#"Previous Steps",
   each [Customer ID] = [#"Customer ID"] and [Year] < Date.Year(DateTime.LocalNow()))) > 0 then "Repeating"
   else "Old"
   
   

1. This formula checks whether the customer has made a purchase in the current year and whether they have made a purchase in previous years. If they have made a purchase in the current year and it's their first purchase, they are labeled "New". If they have made a purchase in the current year and have made purchases in previous years, they are labeled "Repeating". If they have not made a purchase in the current year and have made purchases in previous years, they are labeled "Old".
2. Click "OK" to add the new column to your table.
3. Add a slicer to your report for the "Year" column.
4. Add a table or matrix visual to your report and include the "Customer name" and the new categorization column.
5. Add a filter to the table/matrix visual to only show the rows where the year matches the slicer selection.

With this setup, you should be able to select a year in the slicer and see which customers are new, repeating, or old for that year.

[lridicki]

Hi, thank you

just addtional question, I am getting this error

"Expression.Error: The name 'Previous Steps' wasn't recognized. Make sure it's spelled correctly."

I do not see where did we defined what are Previos Steps maybe I am missing something.

[DOLEARY85]

Hi,

try this measure:

Measure = if(and(CALCULATE(max('Table'[Year]) = YEAR(TODAY() )),CALCULATE(SUMX('Table','Table'[Cust Id]),'Table'[Year] = YEAR(TODAY() ))=1),"New","Repeating")

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

[Alf94]

Hi @lridicki

I think you can find answers to your questions in this article : https://www.daxpatterns.com/new-and-returning-customers/

Best,

[lridicki]

Hi,

Thank you.

I will check this articul. 

BR