shopping recurrence insights

[Rafaelpalma]

Good Morning!

Guys, I need some help. I need to create some shopping recurrence insights.

For example: in the first month I had 500 sales, in the second 400, 40 sales are people who repurchased. In the third month I had 600 purchases, with 45 repurchases.

1 month = 500 purchases
2 months = 400 purchases, with 40 referring to the 500 of the 1 month;
3 month = 600 purchases, with 45 referring to the 600 of the 2 month;

I would like to have any suggestions on how to set this up, please.

[Rafaelpalma]

Hi, can someone help me?

[v-chuncz-msft]

@Rafaelpalma 

You may check the following links.

• https://community.powerbi.com/t5/Desktop/Want-to-calculate-number-of-new-customers-per-every-month-o...
• https://www.daxpatterns.com/new-and-returning-customers/

[Rafaelpalma]

Hi,
I tried to use the site you gave me, but it just returns the total. I need it month to month

The dax:

# New Customers =
VAR CustomersWithNewDate =
CALCULATETABLE (
ADDCOLUMNS (
VALUES (Mov_Site [CodigoCliente]), 
"@NewCustomerDate", [Date New Customer]
),
Allselected (Dados)

)
VAR CustomersWithLineage = TREATAS (
CustomersWithNewDate CustomersWithNewDate, 
Mov_Site [CodigoCliente], 
Data [data] 
)
Result VAR =
CALCULATE (
DISTINCTCOUNT (Cad_Site [CodigoCliente]), 
KEEPFILTERS (CustomersWithLineage)
)
RETURN
Result

Another point is that I didn't understand why I have a date table. So I created a query in my date column and tried to use it like this. This way dax looks at the date table/query and my table "mov_site)

Can you help me?

[PaulOlding]

Hi @Rafaelpalma 

An approach you could take is to get a list of customers for this month, get a list of customers for the previous month, then count how many customers are in both.

So, something like this if you're using it in a visual slicing by month:

Recurring Customer Count = 
VAR _ThisMonthCustomers = VALUES('Table'[CustomerID])
VAR _PreviousMonthCustomers = 
CALCULATETABLE(
    VALUES('Table'[CustomerID]),
    DATEADD('Date'[Date], -1, MONTH)
)
VAR _RecurringCustomers = INTERSECT(_ThisMonthCustomers, _PreviousMonthCustomers)
VAR _Result = COUNTROWS(_RecurringCustomers)
RETURN
_Result

[Rafaelpalma]

Hi, thanks for help!

I tried this, but the following error is giving an error: Error Message: MdxScript(Model) (6, 67) Calculation error in measure 'Mov_Site'[Contagem de Recorrencia]: A date column containing duplicate dates was specified in the call to the 'DATEADD' function. This is not supported.

The dax:

Contagem de recorrência =
var _essemes = VALUES(Mov_Site[CodigoCliente])
var _mespassado = CALCULATETABLE(VALUES(Mov_Site[CodigoCliente]), DATEADD('Mov_Site'[DataEmissao], -1, month)
)
var _clientesrecorrentes = intersect(_essemes,_mespassado)
var _result = COUNTROWS(_clientesrecorrentes)
return
_result

I tried the one below too, but it didn't work.

Contagem de recorrência =
var _essemes = VALUES(Mov_Site[CodigoCliente])
var _mespassado = CALCULATETABLE(VALUES(Mov_Site[CodigoCliente]), DATEADD(values('Mov_Site'[DataEmissao]), -1, month)
)
var _clientesrecorrentes = intersect(_essemes,_mespassado)
var _result = COUNTROWS(_clientesrecorrentes)
return
_result

What to do?

[PaulOlding]

DATEADD is one of the time intelligence functions which require a separate date table to work.  You'll use the date column from the date table rather than 'Mov_Site'[DataEmissao]

Here's an extract from DAX Guide that explains in more detail:

"In order to use any time intelligence calculation, you need a well-formed date table. The Date table must satisfy the following requirements:

• All dates need to be present for the years required. The Date table must always start on January 1 and end on December 31, including all the days in this range. If the report only references fiscal years, then the date table must include all the dates from the first to the last day of a fiscal year. For example, if the fiscal year 2008 starts on July 1, 2007, then the Date table must include all the days from July 1, 2007 to June 30, 2008.
• There needs to be a column with a DateTime or Date data type containing unique values. This column is usually called Date. Even though the Date column is often used to define relationships with other tables, this is not required. Still, the Date column must contain unique values and should be referenced by the Mark as Date Table feature. In case the column also contains a time part, no time should be used – for example, the time should always be 12:00 am.
• The Date table must be marked as a date table in the model, in case the relationship between the Date table and any other table is not based on the Date."

This article goes through the process of creating a Date table and the relationship to another table.

https://www.softcrylic.com/blogs/power-bi-for-beginners-how-to-create-a-date-table-in-power-bi/ 

[Rafaelpalma]

Hi! Thanks.

it worked, but only the total appears, when I put it to come month by month, it's giving an error.

Below a example:

I need to replicate the table above in power BI.
Example: 01/Nov/16 is the total for that month and 01/Dec/16, the value 48 is the recurrence over 01/Nov/16.
Following the same reasoning, the value of 59 of 01/Jan/2021 is above 01/Nov/16.

The focus is always on the first purchase and see how many times it has returned in the past.

Even, rereading my text above, I can see that I expressed myself wrong above. My apologies.

In short, I need to create a DAX to identify how many times that person has returned after the first purchase.

Thanks a lot for the help. you are help me a lot.