Calculate a retention rate through years

[Nicolas_Schonau]

Hello All!

I have an issue with my current data set, I have been looking around on the forum but cannot find something similar to my situation (if this already exists, sorry for the double post!), so here it is :

In my database I have, for each client, the start date of the activity the have purchased. The (simplified here) data set contains the client ID (A,B, C, ...) in my first column, and in the second column the start date, e.g. : 

Client	Start activity
A	2018-07-30 00:00:00
B	2018-08-06 00:00:00
C	2018-07-23 00:00:00
A	2019-07-08 00:00:00
C	2019-07-15 00:00:00
D	2019-07-22 00:00:00

I would like to be able to automatically calculate the retention rate of my clients per year, i.e. to know that on my three clients of 2019 (A, C, D), two (A, C) already purchased some activities in 2018. Maybe it's really easy, but honestly I'm a bit stuck on this right now. Thanks in advance for your help!

[Harpreet1405_12]

Hello,

I want to calculate the retention rate year by year filtered by a specific field.

The data I have is only the total number at the end of the year for example:-

2020-2021=763

2021-2022=553

2022-2023=306

The retention rate must be change when we select the name from the drop down filter.

[v-yulgu-msft]

Hi @Nicolas_Schonau,

New a date dimention table which is linked to source table ('Sheet2' in my test) based on [Date] field.

Dim date = CALENDAR(MIN(Sheet2[Start activity]),MAX(Sheet2[Start activity]))

Create measures similar to below.

countclient TY = DISTINCTCOUNT(Sheet2[Client])

countclient LY =
CALCULATE (
    DISTINCTCOUNT ( Sheet2[Client] ),
    FILTER (
        ALLSELECTED ( 'Dim date' ),
        'Dim date'[Date].[Year]
            = SELECTEDVALUE ( 'Dim date'[Date].[Year] ) - 1
    )
)

is purchased LY =
CALCULATE (
    COUNT ( Sheet2[Client] ),
    FILTER (
        ALLSELECTED ( Sheet2 ),
        YEAR ( Sheet2[Start activity] )
            = YEAR ( SELECTEDVALUE ( 'Dim date'[Date] ) ) - 1
            && Sheet2[Client] = SELECTEDVALUE ( Sheet2[Client] )
    )
)

retention client =
COUNTROWS (
    FILTER ( ALLSELECTED ( Sheet2[Start activity] ), [is purchased LY] >= 1 )
)

retention rate = [retention client]/[countclient LY]

Best regards,

Yuliana Gu

[Nicolas_Schonau]

Hello @v-yulgu-msft,

Thank you for your answer.

I have been able to create the new 'Dim date' table, and the first measure countclient TY, but not the second one, counclient LY. This gives me an empty measure. I think the problem comes from the fact that in the formula, Power Bi doesn't allow me to insert the 

.[Year]

If i use it, I'm getting an error, and if I don't, I'm getting an empty measure. Thi blocks me for the rest of the measures.

Could it be some kind of format-related issue? Did you get this too?

Thanks in advance for your answer.

Best regards,

Nicolas

[v-yulgu-msft]

Hi @Nicolas_Schonau,

Please modify the formula to below for a test.

countclient LY =
CALCULATE (
    DISTINCTCOUNT ( Sheet2[Client] ),
    FILTER (
        ALLSELECTED ( 'Dim date' ),
        YEAR('Dim date'[Date])
            = YEAR(SELECTEDVALUE ( 'Dim date'[Date])) - 1
    )
)

Regards,

Yuliana Gu

[Nicolas_Schonau]

Hello @v-yulgu-msft,

I have tried your new solution but couldn't succeed in getting the correct results.

I have uploaded the test .pbix file I use for this example, would you be so kind to have a quick look at it and see what's wrong in what I'm doing ?

https://nschonau-my.sharepoint.com/:u:/g/personal/nschonau_nschonau_onmicrosoft_com/EbMUd2jPnEdIox6s...

Once again, many thanks in advance.

Nicolas

[Nicolas_Schonau]

Hi @v-yulgu-msft,

I just wanted to know if you had the opportunity to take a look at the data set ?

I have tried again with some modifications but this still does not work.

Many thanks in advance.

Best regards,

Nicolas

[Nicolas_Schonau · Accepted solution]

Hi All,

I finally managed to find a solution here. For the record, here's how I did it:

First, create a new Dim date Table with first column being:

Dim date = CALENDAR(MIN(Sheet2[Start activity]);MAX(Sheet2[Start activity]))

And a second column to calculate the year: 

Year = YEAR('Dim date'[Date])

Do not forget to mark the table as a date table, and link it to the main table.

Second, create these measures in the main table:

Customers = DISTINCTCOUNT ( Sheet2[Client] )

New Customers = 
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            VALUES ( Sheet2[Client] );
            "PreviousSales"; CALCULATE (
                COUNTROWS ( Sheet2 );
                FILTER (
                    ALL ( 'Dim date' );
                    'Dim date'[Date] < MIN ( 'Dim date'[Date] )
                )
            )
        );
        [PreviousSales] = 0
    )
)

Returning Customers = 
COUNTROWS (
    CALCULATETABLE (
        VALUES ( Sheet2[Client] );
        VALUES ( Sheet2[Client] );
        FILTER (
            ALL ( 'Dim date' );
            'Dim date'[Date] < MIN ( 'Dim date'[Date] )
        )
    )
)

Customers Last Year = 
CALCULATE (
    DISTINCTCOUNT ( Sheet2[Client] );
    FILTER (
        ALLSELECTED ( 'Dim date' );
        'Dim date'[Year]
            = SELECTEDVALUE ( 'Dim date'[Year] ) - 1

    )
)

retention rate = [Returning Customers] / [Customers Last Year]

This will give you the following matrix :

Nicolas

[kshaham]

Hello,

It's not working for me. I wanted to find the Retention Rate for those clients who purchase last year as well as current year. Not for all the clients.