Customer Retation

[Anonymous]

Hello guys,
I have a database with Customer Start and End data and I would like to find the rate per Month/year.
How could I get this rate right?
I'm using the following measurements to arrive at the retention rate.

start of period =

CALCULATE(

[T - Cliente Acumulado],

DATEADD('dCalendário'[Date],-1,MONTH))

new customers  =

Calculate(

COUNT(dClientes[Id_card]),

USERELATIONSHIP('dCalendário'[Date],dClientes[Data_inicio]))

Churn =

CALCULATE(

[Clientes Perdidos],

FILTER(

ALL('dCalendário'),

'dCalendário'[Date] <= MAX('dCalendário'[Date])),

USERELATIONSHIP('dCalendário'[Date],dClientes[Data_inicio]))

end of period = ((Medidas_OKRs[start of period ]-[churn])+[new customers])

Retention rate =

DIVIDE([end of period]-[new customers],Medidas_OKRs[start of period ])

 Below is the database as an example

Customer	Franchise	Start Date	final date
Dajabba Saraava Furruara	aago Sabbva Doubbho assas	1/31/2022
Durtsyj DurtafaDaDao Dagatabb bbtda	aago Sabbva Doubbho assas	11/7/2021
bbaduraDo SurvaDos uspuDaaas andustraa u DojurDao Du Furro uarubba	aago Sabbva Doubbho assas	11/12/2021
Naabbs FabraDaDao DastrabuaDao DojurDao Du Produtos DosjutaDos bbtda	aago Sabbva Doubbho assas	11/10/2021
Nuw Donsubbtoraa u assussoraa uarubba	aago Sabbva Doubbho assas	11/30/2021
P G S jovuas bbtda	aago Sabbva Doubbho assas	11/3/2021	2/9/2022
San FranDasDo Donstrutora bbotuadora u anDorporadora bbtda	aago Sabbva Doubbho assas	12/23/2021	1/20/2022
Studao juraka arquatutura u Dusagn bbtda	aago Sabbva Doubbho assas	11/22/2021

Customer	Franchise	Start Date	final date
aqua Tuj Papubbaraa u anforjataDa bbtda	abbason Truppubb a. F. dos Santos	3/27/2020	7/30/2021
D.V. Do Prado Fortunato	abbason Taago Rodraguus bbaja	8/31/2021	11/11/2021
Dandaan ungunharaa bbtda - Transação	abbason Truppubb a. F. dos Santos	6/4/2021	7/14/2021
Darbbos Hunraquu bbuatu Puruara	abbason Taago Rodraguus bbaja	8/24/2021
Darobbana Guurruaro Bazar - Transação	abbason Truppubb a. F. dos Santos	4/30/2021	5/25/2021
Dusta Du abbajuntos Brasabb bbtda	abbason Taago Rodraguus bbaja	2/25/2022
D S jontuaro uarubba	abbason Taago Rodraguus bbaja	9/3/2021	12/12/2021
u P abbvus Furrajuntas u ansujos	abbason Taago Rodraguus bbaja	11/26/2021
usDobba Du Dajpuous unsano u Dusunvobbvajunto Profassaonabb bbtda.	abbason Taago Rodraguus bbaja	10/3/2022
Gubbato Dubb Gaorno bbtda	abbason Taago Rodraguus bbaja	1/13/2022
anstatuto Brasabbuaro Du Gustao ujprusaraabb - abguj	abbason Taago Rodraguus bbaja	9/7/2021
j4 ungunharaa u DonstruDous bbtda	abbason Taago Rodraguus bbaja	7/3/2022
jarduk uvuntos u ProduDous artastaDas u Dubbturaas bbtda	abbason Taago Rodraguus bbaja	9/3/2021	12/31/2021
Pousada Bahaa 10	abbason Truppubb a. F. dos Santos	6/30/2020	4/12/2021
Pousada Bahaa BaDana	abbason Truppubb a. F. dos Santos	6/30/2020	4/12/2021
Pousada Bahaa Tajbor	abbason Truppubb a. F. dos Santos	6/30/2020	4/12/2021
Pousada Vabba Da Barra	abbason Truppubb a. F. dos Santos	6/30/2020	4/12/2021
Saj SuguranDa ubbutronaDa bbtda	abbason Taago Rodraguus bbaja	10/29/2021
Sas Do Brasabb DojurDaabb u SurvaDos uarubba	abbason Taago Rodraguus bbaja	1/31/2022
TuDnobboggu SobbuDous uj anforjataDa uarubba - Transação	abbason Truppubb a. F. dos Santos	12/14/2020	3/29/2021

[v-kkf-msft]

Hi @Anonymous ,

Has your problem been solved? If it is solved, please mark a reply which is helpful to you.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[v-kkf-msft · Accepted solution]

Hi @Anonymous ,

I am not sure if I understood your question correctly. Please try the following measures:

EndCount = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Customer] ),
    FILTER (
        'Table',
        'Table'[Start Date] <= MAX ( Dates[Date] )
            && COALESCE ( 'Table'[final date], TODAY () ) > MAX ( Dates[Date] )
    )
)

NewCount = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Customer] ),
    FILTER (
        'Table',
        'Table'[Start Date] >= MIN ( Dates[Date] )
            && 'Table'[Start Date] <= MAX ( Dates[Date] )
            && COALESCE ( 'Table'[final date], TODAY () ) > MAX ( Dates[Date] )
    )
)

StartCount = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Customer] ),
    FILTER (
        'Table',
        'Table'[Start Date] < MIN ( Dates[Date] )
            && COALESCE ( 'Table'[final date], TODAY () ) >= MIN ( Dates[Date] )
    )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

" I would like to find the rate per Month/year"

Your source data is on day level granularity.  You will need to explain the rules for partial months and partial years.  How should these be calculated?

[Anonymous]

@lbendlin 

They should be calculations by month but I don't know how to do that

[lbendlin]

You need to decide if a month should be counted no matter when in the month the event happened (could be on the last day) or if it should be prorated based on the number of days in that month.

[Anonymous]

I can consider the last day. How would I do this measurement?