Creating a retention table

[Eyal_H]

I have this table as seen in the picture. 

I'm trying to create a retention table, with the end result being somewhat similar to the example I sent in the second picture.

In my case what I'm trying to do is - 

Above the table i'll have a filter to choose capability_name.

When I choose a capability_name, i'll see a retention table for it, that counts the number of unique companies.

timeline should be on a weekly basis, with the first date being the earliest usage_week for that capability_name (which is when the capability was published)

So for example - I choose capability 'A' in the filter, i'll see the retention of unique companies from the earliest usage_week. first week will obviously be 100% as it's the first week, and from that week on i'll see a % of how many companies are still using that capability_name on a weekly basis.

What are the steps to achieve this goal?

[Eyal_H]

Thank you for the quick reply!
What I'm missing in what you sent is - I don't see the retention data. for example, when you chose capability A, in 1/1/24 - you have a 100% at the beggining on week 1 as expected, but then on week 2, 3 and 4 I'd also expect to see the retention % ( how many unique companies are still using it ).

[Anonymous]

Hi @Eyal_H ,

Sorry for that the information you have provided is not making the problem clear to me.  Is that what you mean? According to my test data, company 1 was there in the first week and company 1 was still there in the second week, so the survival rate is 100%. If that's what you mean, you just need to place the fields as shown below.

If I have misunderstood you, please provide example data, logic and expected results.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Neeko Tang

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

[Eyal_H]

What I meant was - I was expecting to see an output like this, where you actually see the retention by week:

[Anonymous]

Hi @Eyal_H ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) Create a date table.

DateTable = 
     CALENDAR(
         MIN('Table'[usage_week]),
         MAX('Table'[usage_week])
     )

(3) Add a calculated column to your main table.

WeekNumber = WEEKNUM('Table'[usage_week], 2)

(4) Create a measure.

Retention = 
VAR FirstWeek = 
    CALCULATE(
        MIN('Table'[WeekNumber]),
        FILTER(
            ALLSELECTED('Table'),
            'Table'[capability_name] = SELECTEDVALUE('Table'[capability_name])
        )
    )
VAR CurrentWeek = MAX('Table'[WeekNumber])
VAR InitialCount = 
    CALCULATE(
        DISTINCTCOUNT('Table'[company_id]),
        FILTER(
            ALLSELECTED('Table'),
            'Table'[WeekNumber] = FirstWeek &&
            'Table'[capability_name] = SELECTEDVALUE('Table'[capability_name])
        )
    )
VAR CurrentCount = 
    CALCULATE(
        DISTINCTCOUNT('Table'[company_id]),
        FILTER(
            ALLSELECTED('Table'),
            'Table'[WeekNumber] = CurrentWeek &&
            'Table'[capability_name] = SELECTEDVALUE('Table'[capability_name])
        )
    )
RETURN
    DIVIDE(CurrentCount, InitialCount, 0)

For more details, please refer:

Customer Retention in Power BI: DAX Measures - RADACAD

Customer Retention Part 3: Period Of Stay – Cohort... - Microsoft Fabric Community

Cohort Analysis with Power BI and SQL | by Olubukunola Akinsola | Medium

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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