Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Monthly retention cohort help needed

I am trying to create a monthly retention cohort with an output that looks like this:

         
CohortStartInitial1M2M3M4M5M6M
ANov 194100%100%75%50%  
BJan 203100%100%66%   
C        
D        

 

I have the following data tables:

(1) date calendar

(2) users calendar -- what is specific about this is that there is only a start date and deactivation date (I do not have a table with purchases -- but i still need a way to count a user in each month until the deactivation date) 

UserIDActivated DateDeactivated DateCohort
111/10/20191/1/2020A
211/10/20192/1/2020A
311/10/2019 A

4

11/10/2019 A
512/10/2019 B
612/10/2019 B
712/10/20192/1/2020B

 

What measures do I need to create and how do I create the cohort table itself? 

5 REPLIES 5
v-lid-msft
Community Support
Community Support

Hi @Anonymous ,

 

We can create a calculated table and measure to meet your requirement:

 

Calculated Table:

 

Column Header = 
UNION (
    DATATABLE (
        "Value", STRING,
        "Rank", INTEGER,
        {
            { "Start", 1 },
            { "Initial", 2 }
        }
    ),
    SELECTCOLUMNS (
        GENERATESERIES ( 1, 10 ),
        "Value", [Value] & "M",
        "Rank", [Value] + 2
    )
)

 

Measure:

 

Value In Matrix =
VAR StartDate =
    MIN ( 'Table'[Activated Date] )
VAR Initial =
    DISTINCTCOUNT ( 'Table'[UserID] )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Column Header'[Value] ),
        "Start", FORMAT ( StartDate, "MMM YY" ),
        "Initial", Initial & "",
        FORMAT (
            DIVIDE (
                CALCULATE (
                    DISTINCTCOUNT ( 'Table'[UserID] ),
                    FILTER (
                        'Table',
                        'Table'[Deactivated Date] = BLANK ()
                            || 'Table'[Deactivated Date]
                                >= DATE ( YEAR ( StartDate ), MONTH ( StartDate ) + MAX ( 'Column Header'[Rank] ) - 2, 1 )
                    )
                ),
                Initial,
                0
            ),
            "0%"
        )
    )

 

10.jpg

 
Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

thank you sir, your solution worked on my project. the only thing that I need to modify is how to replace same percentage value after 1M, I need it to be blank. Hope you could give direction. thanks again

Anonymous
Not applicable

hi @v-lid-msft thanks for your help here. I am wondering, how would I adjust your measures if I wanted to do this weekly instad of monthly? 

Hi @Anonymous ,

 

We can adjust the formula as following to meet your requirement:

 

Calculated Table: 

Column Header = 
UNION (
    DATATABLE (
        "Value", STRING,
        "Rank", INTEGER,
        {
            { "Start", 1 },
            { "Initial", 2 }
        }
    ),
    SELECTCOLUMNS (
        GENERATESERIES ( 1, 100 ),
        "Value", [Value] & "W",
        "Rank", [Value] + 2
    )
)

 

Measure:

Value In Matrix = 
VAR StartDate =
    MIN ( 'Table'[Activated Date] )
VAR Initial =
    DISTINCTCOUNT ( 'Table'[UserID] )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Column Header'[Value] ),
        "Start", FORMAT ( StartDate, "MMM YY" ),
        "Initial", Initial & "",
        FORMAT (
            DIVIDE (
                CALCULATE (
                    DISTINCTCOUNT ( 'Table'[UserID] ),
                    FILTER (
                        'Table',
                        'Table'[Deactivated Date] = BLANK ()
                            || 'Table'[Deactivated Date]
                                >= StartDate + (MAX('Column Header'[Rank])-3)*7
                    )
                ),
                Initial,
                0
            ),
            "0%"
        )
    )

 

18.jpg


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

i have a new table I working with here and this formula is not working. I have a Cohort called Cohort C with 4 unique users in that. They all started on Feb 16, 2020. I want to record the percentage of the cohort that had status 1 in a given week. This is the table:

UserIDStatusIDDateUpdated
10112/16/2020
8812/20/2020
8812/20/2020
8812/22/2020
8812/24/2020
8812/24/2020
8812/24/2020
8812/24/2020
10613/1/2020

106

13/1/2020
10613/1/2020
10613/1/2020
10613/1/2020
8813/2/2020
8813/2/2020

My cohort report is giving me the following numbers in W1, W2,W3 respectively: 75%, 50%, 50% WHEN it SHOULD be 50%, 25%, 50% (if you were starting as sunday as start of week one) OR 25%, 25%, 50%, 25% if you were starting the week on Monday @v-lid-msft can you please help? 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.