Creating a retention table

Question

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?

anonymous · Answer

Hi&nbsp;Eyal_H&nbsp;,
&nbsp;
According to your description, here are my steps you can follow as a solution.
(1) This is my test data.&nbsp;

(2) Create a date table.
DateTable = 
     CALENDAR(
         MIN('Table'[usage_week]),
         MAX('Table'[usage_week])
     )

(3)&nbsp;Add a calculated column to your main table.
WeekNumber = WEEKNUM('Table'[usage_week], 2)
(4)&nbsp;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 &amp;&amp;
            'Table'[capability_name] = SELECTEDVALUE('Table'[capability_name])
        )
    )
VAR CurrentCount = 
    CALCULATE(
        DISTINCTCOUNT('Table'[company_id]),
        FILTER(
            ALLSELECTED('Table'),
            'Table'[WeekNumber] = CurrentWeek &amp;&amp;
            '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
&nbsp;
If the above one can't help you get the desired result, please provide some&nbsp;sample data&nbsp;in your tables (exclude&nbsp;sensitive&nbsp;data) with&nbsp;Text&nbsp;format and your&nbsp;expected result&nbsp;with backend logic and special examples.&nbsp;It is better&nbsp;if you can share a&nbsp;simplified&nbsp;pbix file.&nbsp;Thank you.
&nbsp;
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.&nbsp;

eyal_h · Answer

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 · Answer

Hi&nbsp;Eyal_H&nbsp;,
&nbsp;
Sorry for that the information you have provided is not making the problem clear to me.&nbsp; 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%.&nbsp;If that's what you mean, you just need to place the fields as shown below.

&nbsp;

&nbsp;
If I have misunderstood you, please provide example data, logic and expected results.
&nbsp;
Refer to:
How to provide sample data in the Power BI Forum
How to Get Your Question Answered Quickly
&nbsp;
Best Regards,
Neeko Tang
If this post &nbsp;helps, then please consider Accept it as the solution&nbsp; to help the other members find it more quickly.&nbsp;