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 exampleCustomerFranchiseStart Datefinal dateDajabba Saraava Furruaraaago Sabbva Doubbho assas1/31/2022 Durtsyj DurtafaDaDao Dagatabb bbtdaaago Sabbva Doubbho assas11/7/2021 bbaduraDo SurvaDos uspuDaaas andustraa u DojurDao Du Furro uarubbaaago Sabbva Doubbho assas11/12/2021 Naabbs FabraDaDao DastrabuaDao DojurDao Du Produtos DosjutaDos bbtdaaago Sabbva Doubbho assas11/10/2021 Nuw Donsubbtoraa u assussoraa uarubbaaago Sabbva Doubbho assas11/30/2021 P G S jovuas bbtdaaago Sabbva Doubbho assas11/3/20212/9/2022San FranDasDo Donstrutora bbotuadora u anDorporadora bbtdaaago Sabbva Doubbho assas12/23/20211/20/2022Studao juraka arquatutura u Dusagn bbtdaaago Sabbva Doubbho assas11/22/2021 CustomerFranchiseStart Datefinal dateaqua Tuj Papubbaraa u anforjataDa bbtdaabbason Truppubb a. F. dos Santos3/27/20207/30/2021D.V. Do Prado Fortunatoabbason Taago Rodraguus bbaja8/31/202111/11/2021Dandaan ungunharaa bbtda - Transaçãoabbason Truppubb a. F. dos Santos6/4/20217/14/2021Darbbos Hunraquu bbuatu Puruaraabbason Taago Rodraguus bbaja8/24/2021 Darobbana Guurruaro Bazar - Transaçãoabbason Truppubb a. F. dos Santos4/30/20215/25/2021Dusta Du abbajuntos Brasabb bbtdaabbason Taago Rodraguus bbaja2/25/2022 D S jontuaro uarubbaabbason Taago Rodraguus bbaja9/3/202112/12/2021u P abbvus Furrajuntas u ansujosabbason Taago Rodraguus bbaja11/26/2021 usDobba Du Dajpuous unsano u Dusunvobbvajunto Profassaonabb bbtda.abbason Taago Rodraguus bbaja10/3/2022 Gubbato Dubb Gaorno bbtdaabbason Taago Rodraguus bbaja1/13/2022 anstatuto Brasabbuaro Du Gustao ujprusaraabb - abgujabbason Taago Rodraguus bbaja9/7/2021 j4 ungunharaa u DonstruDous bbtdaabbason Taago Rodraguus bbaja7/3/2022 jarduk uvuntos u ProduDous artastaDas u Dubbturaas bbtdaabbason Taago Rodraguus bbaja9/3/202112/31/2021Pousada Bahaa 10abbason Truppubb a. F. dos Santos6/30/20204/12/2021Pousada Bahaa BaDanaabbason Truppubb a. F. dos Santos6/30/20204/12/2021Pousada Bahaa Tajborabbason Truppubb a. F. dos Santos6/30/20204/12/2021Pousada Vabba Da Barraabbason Truppubb a. F. dos Santos6/30/20204/12/2021Saj SuguranDa ubbutronaDa bbtdaabbason Taago Rodraguus bbaja10/29/2021 Sas Do Brasabb DojurDaabb u SurvaDos uarubbaabbason Taago Rodraguus bbaja1/31/2022 TuDnobboggu SobbuDous uj anforjataDa uarubba - Transaçãoabbason Truppubb a. F. dos Santos12/14/20203/29/2021

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,WinnizIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous

Not applicable

4 years ago

Solved

Customer Retation

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

Need Help

v-kkf-msft

4 years ago

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.

Customer Retation.pbix75 KB

6 Replies

lbendlin
Super User
4 years ago
" 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
  Not applicable
  4 years ago
  lbendlin
  They should be calculations by month but I don't know how to do that
  - lbendlin
    Super User
    4 years ago
    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.

v-kkf-msft

Community Support

4 years ago

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] )
    )
)

Customer Retation.pbix75 KB

v-kkf-msft
Community Support
4 years ago
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

Forum Discussion

Customer Retation

6 Replies

Related content

Customer Scorecard

Custom Visual

Customize subtotal in matrix

Custom Endpoints - Eventstream

Custom Matrix Visual