cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## DAX - Customer retention

Calculate the sum of customers who bought and stopped buying in the last 12 months, and returned to buy in the quarter.

@tamerj1  do you have any idea?

1 ACCEPTED SOLUTION
Super User

Please try the following code. To be honest I have no idea whether it works or not. However I tried to optimize it only by sense as I have no data to work with and measure the actual performance. This is a heavy code which in total requires a great number of context transitions and multiple iterations over the complete table. This iteration cannot be avoided otherwise there is no other way to gain access to each individual date which is required to calculate the period gap between each two consecutive transactions of the same customer. You might find it a bet complex but in fact it is simple and I hope it works.

Further you shall be able to use the same code to flag the customers who matches this criteria with the flexibility to filter different ranges of date (You can use a time slider based on dCalendar[Date] to select start and end dates of the selected period)

``````Teste aula =
SUMX (
CALCULATETABLE (
VALUES ( SBOPRODMS[Código do cliente] ),
SBOPRODMS[Documento] = "Nota fiscal de saída"
),
VAR CurrentCustomerTable =
CALCULATETABLE ( SBOPRODMS, SBOPRODMS[Documento] = "Nota fiscal de saída" )
VAR T1 =
CurrentCustomerTable,
"Period",
VAR CurrentDate =
SBOPRODMS[Data Base]
VAR PreviousDate =
MAXX ( FILTER ( CurrentCustomerTable, SBOPRODMS[Data Base] < CurrentDate ), SBOPRODMS[Data Base] )
RETURN
IF ( NOT ISBLANK ( PreviousDate ), DATEDIFF ( PreviousDate, CurrentDate, DAY ) )
)
VAR MaxiDisengagementPeriod =
MAXX ( T1, [Period] )
RETURN
IF ( MaxiDisengagementPeriod > 365, 1 )
)``````

4 REPLIES 4
Community Support

Can you provide some sample data or simple pbix files? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Solution Specialist

Do you mean Calculate New, Returning, Lost, and Recovered Customers in #dax?

https://www.daxpatterns.com/new-and-returning-customers/

And if your dateset is very large, I have another way.

Super User

Please try the following code. To be honest I have no idea whether it works or not. However I tried to optimize it only by sense as I have no data to work with and measure the actual performance. This is a heavy code which in total requires a great number of context transitions and multiple iterations over the complete table. This iteration cannot be avoided otherwise there is no other way to gain access to each individual date which is required to calculate the period gap between each two consecutive transactions of the same customer. You might find it a bet complex but in fact it is simple and I hope it works.

Further you shall be able to use the same code to flag the customers who matches this criteria with the flexibility to filter different ranges of date (You can use a time slider based on dCalendar[Date] to select start and end dates of the selected period)

``````Teste aula =
SUMX (
CALCULATETABLE (
VALUES ( SBOPRODMS[Código do cliente] ),
SBOPRODMS[Documento] = "Nota fiscal de saída"
),
VAR CurrentCustomerTable =
CALCULATETABLE ( SBOPRODMS, SBOPRODMS[Documento] = "Nota fiscal de saída" )
VAR T1 =
CurrentCustomerTable,
"Period",
VAR CurrentDate =
SBOPRODMS[Data Base]
VAR PreviousDate =
MAXX ( FILTER ( CurrentCustomerTable, SBOPRODMS[Data Base] < CurrentDate ), SBOPRODMS[Data Base] )
RETURN
IF ( NOT ISBLANK ( PreviousDate ), DATEDIFF ( PreviousDate, CurrentDate, DAY ) )
)
VAR MaxiDisengagementPeriod =
MAXX ( T1, [Period] )
RETURN
IF ( MaxiDisengagementPeriod > 365, 1 )
)``````

Helper II

Hello friend, I got the formula below in contact with a teacher. I will test yours because it seems smaller and more practical.

``````Reativacao Clientes =
//Calcula os clientes do Intervalo 3
VAR vClientes_trimestre_filtro =
CALCULATETABLE(
VALUES(fVendas[Código do cliente]),
fVendas[Documento] = "Nota fiscal de saída"
)
// Calcula a data início do intervalo 1
VAR vData_inicio = CALCULATE(MIN(fVendas[Data Base]), REMOVEFILTERS(dCalendario))

// Calcula a data máxima do Intervalo 1
VAR vData_fim =
EOMONTH(
CALCULATE(
MAX(dCalendario[Date]),
),
-1
)
// Calcula os clientes do intervalo 1
VAR vClientes_intervalo_1 =
CALCULATETABLE(
VALUES(fVendas[Código do cliente]),
fVendas[Documento] = "Nota fiscal de saída",
FILTER(
ALL(dCalendario[Date]),
dCalendario[Date] >= vData_inicio &&
dCalendario[Date] <= vData_fim
)
)
// Calcula a data máxima do Intervalo 2
VAR vData_max_trimestre_anterior =
CALCULATE(
MAX(dCalendario[Date]),
)
// Calcula os clientes do intervalo 2
VAR vClientes_intervalo_2 =
CALCULATETABLE(
VALUES(fVendas[Código do cliente]),
fVendas[Documento] = "Nota fiscal de saída",
FILTER(
ALL(dCalendario[Date]),
dCalendario[Date] > vData_fim &&
dCalendario[Date] <= vData_max_trimestre_anterior
)
)
// Calcula os clientes que compraram no intervalo 3 e no intervalo 1
VAR vClientes_intervalo_3_e_1 = INTERSECT(vClientes_trimestre_filtro, vClientes_intervalo_1)

// Calculo os clientes que compraram no 3 e no 1, retirando os do intervalo 2
EXCEPT(vClientes_intervalo_3_e_1, vClientes_intervalo_2)

RETURN

support worksheet:

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors