Need help with Member Retention based off 2 columns

[OpenMike13]

Hello All

Im having issues finding member rention based off the data i was given. Here is a link to some sample data.

I have two date columns to go off of a countdate column- which is the day we count membership each month this comes from a calendarlookup table.

and a expiration date-which is the date the membership expires.  if they renew a new expiration date is issued

I have 2 users in the sample data Jake Smith and Tim Wilson. Jake smith was a member from countdate 1/15/2020 to 6/15/2021 and dropped off/was not a member again until 1/15/2022 and reissued another expiration date.

Tim Wilson was a member from countdate 10/15/2020 to 9/15/2021 and his expiration date was 9/30/2021 and he has not renewed since.

Is there a way to calculate the retention from this information and calculate this for each count date? Thanks in advanced

[Anonymous · Accepted solution]

Hi @OpenMike13 ,

Please try this measure.

Member retention Rate = 
VAR _mindate =
    CALCULATE ( MIN ( 'Table'[CountDate] ), ALL ( 'Table'[CountDate] ) )
VAR _count1 =
    COUNTROWS ( 'Table' )
VAR _count2 =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[contactid] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[CountDate] >= _mindate
                && 'Table'[CountDate] <= MIN ( 'Table'[CountDate] )
        )
    )
VAR _rate = DIVIDE(_count1,_count2)
RETURN
    _rate

Attached PBIX file for reference.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[lbendlin]

what is your expected result based on the sample data you provided?