cancel
Showing results for
Did you mean:
Helper II

## DAX - Last year customer retention

I have a measure that calculates customer retention: customers who bought in the last 3 months. I need to bring the comparison of this measure from the previous year. I tried the formula below without success.

customer retention: customers who bought in the last 3 months.

``````Frequencia pedidos Trimestral =
VAR AllOldCustomers =
CALCULATETABLE (
VALUES ( fVendas[Nome do PN] ),
fVendas,
fVendas[Data NF]
< TODAY () - 90,
fVendas[Documento] = "Nota fiscal de saída"
)
VAR AllNewCustomers =
CALCULATETABLE (
VALUES ( fVendas[Nome do PN] ),
fVendas[Data NF]
< TODAY () ,
fVendas[Documento] = "Nota fiscal de saída"
)
RETURN
COUNTROWS ( EXCEPT ( AllNewCustomers, AllOldCustomers ) )``````

comparison of this measure from the previous year:

``````Frequencia pedidos Trimestral PY =
VAR PreviousYearDate = DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) )

VAR AllOldCustomers =
CALCULATETABLE (
VALUES ( fVendas[Nome do PN] ),
fVendas,
fVendas[Data NF]
< PreviousYearDate - 90,
fVendas[Documento] = "Nota fiscal de saída"
)
VAR AllNewCustomers =
CALCULATETABLE (
VALUES ( fVendas[Nome do PN] ),
fVendas[Data NF]
< PreviousYearDate ,
fVendas[Documento] = "Nota fiscal de saída"
)
RETURN
IF ( ISBLANK ( ( COUNTROWS ( EXCEPT ( AllNewCustomers, AllOldCustomers ) ) ) ), 0, COUNTROWS ( EXCEPT ( AllNewCustomers, AllOldCustomers ) ) ) ``````

1 ACCEPTED SOLUTION
Community Champion

Hi @Diego_Vialle
This should work. I see no problem except that this formula is not supposed to be sliced by date (which has no meaning). You can use it to flag customers or in a card visual. Here is the same formula with minor cozmatics

``````Frequencia pedidos Trimestral PY =
VAR PreviousYearDate =
DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) )
VAR AllOldCustomers =
CALCULATETABLE (
VALUES ( fVendas[Nome do PN] ),
fVendas[Data NF] < PreviousYearDate - 90,
fVendas[Documento] = "Nota fiscal de saída"
)
VAR AllNewCustomers =
CALCULATETABLE (
VALUES ( fVendas[Nome do PN] ),
fVendas[Data NF] < PreviousYearDate,
fVendas[Documento] = "Nota fiscal de saída"
)
RETURN
COALESCE ( COUNTROWS ( EXCEPT ( AllNewCustomers, AllOldCustomers ) ), 0 )``````
3 REPLIES 3
Community Champion

Hi @Diego_Vialle
This should work. I see no problem except that this formula is not supposed to be sliced by date (which has no meaning). You can use it to flag customers or in a card visual. Here is the same formula with minor cozmatics

``````Frequencia pedidos Trimestral PY =
VAR PreviousYearDate =
DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) )
VAR AllOldCustomers =
CALCULATETABLE (
VALUES ( fVendas[Nome do PN] ),
fVendas[Data NF] < PreviousYearDate - 90,
fVendas[Documento] = "Nota fiscal de saída"
)
VAR AllNewCustomers =
CALCULATETABLE (
VALUES ( fVendas[Nome do PN] ),
fVendas[Data NF] < PreviousYearDate,
fVendas[Documento] = "Nota fiscal de saída"
)
RETURN
COALESCE ( COUNTROWS ( EXCEPT ( AllNewCustomers, AllOldCustomers ) ), 0 )``````
Helper II

You're right again, for this formula it doesn't make sense to have a date filter. I need another formula then for repeat customers.

Super User

@Diego_Vialle , You need measure like

rolling 3 =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = date(Year(_max), month(_max) -3, Day(_max))+1,
BLANK())
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

rolling 3 last year =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = date(Year(_max)-1, month(_max), Day(_max))
var _min = date(Year(_max), month(_max) -3, Day(_max))+1,
BLANK())
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

retained = countx(values(Customer[Customer]), if(not(isblank([rolling 3 last year])) && not(isblank([rolling 3])) , Customer[Customer], blank()))

lost = countx(values(Customer[Customer]), if(not(isblank([rolling 3 last year])) && (isblank([rolling 3])) , Customer[Customer], blank()))

new = countx(values(Customer[Customer]), if((isblank([rolling 3 last year])) && not(isblank([rolling 3])) , Customer[Customer], blank()))

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...

Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Announcements

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

#### Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!