Forum Discussion

OpenMike13's avatar
OpenMike13
Frequent Visitor
4 years ago
Solved

Need help with Member Retention based off 2 columns

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

 

 

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

2 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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