Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Hi, can someone help me?
You may check the following links.
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?
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
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?
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:
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/
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |