Player Retention Rates

[RNZALR]

Hello,

I’m looking for some dax that will calculate retention rates for me. I’m building a report for a sports body where the season starts on 1 November each year. I have a table with player registration data with Player ID’s across about 9 seasons. I want to be able to calculate if a player has returned for a new season or whether they are new for that season by being able to find their ID in the prior season. If their ID is found in the prior season then they are considered a Re-Committed Player. If their ID doesn’t exist in the prior season then they are a New Player. A third category would be a player who doesn’t exist in the prior season but did exist in any season before that. They would then be a Returning Player.

The videos I’ve seen don’t seem to do exactly what I’m wanting to as they’re using different date ranges and/or looking back over a fixed period e.g. 90 days. I need it to look back over the prior season which might start 365 or 366 days ago depending on if it was a leap year or not.

Any guidance would be hugely appreciated. 

[Jihwan_Kim]

Hi, 

I am not sure how your data model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

It is for creating a measure.

I hope the below can provide some ideas on how to create a solution for your data model.

Player status measure: =
VAR _prioryear =
    MAX ( 'Calendar'[Year] ) - 1
VAR _eachyearcondition =
    COUNTROWS ( Registration )
VAR _prioryearcondition =
    CALCULATE ( COUNTROWS ( Registration ), 'Calendar'[Year] = _prioryear )
VAR _previousyearscondition =
    CALCULATE ( COUNTROWS ( Registration ), 'Calendar'[Year] < _prioryear )
RETURN
    IF (
        HASONEVALUE ( Player[Player ID] ) && HASONEVALUE ( 'Calendar'[Year] ),
        SWITCH (
            TRUE (),
            _prioryearcondition = 0
                && _previousyearscondition = 0
                && _eachyearcondition = 1, "New",
            _prioryearcondition = 1
                && _eachyearcondition = 1, "Re-committed",
            _prioryearcondition = 0
                && _previousyearscondition >= 1
                && _eachyearcondition = 1, "Return",
            "Not registered"
        )
    )

[RNZALR]

This looks great!  My follow up question is how do I get it to count the different outputs each season?  I want to be able to sum the "New", "Re-Committed" and "Return" totals by season and then be able to divide each one by the total registered players for that season in order to get a percentage of players returning, re-committing and joining for the first time.  I can't quite seem to work out how to do that from your code.  Any advice would be excellent.

This is the amended dax I have used in my model:

Player status measure: =

VAR _prioryear =

    MAX ( 'Date Table'[Year] ) - 1

VAR _eachyearcondition =

    COUNTROWS ( 'Player Registrations' )

VAR _prioryearcondition =

    CALCULATE ( COUNTROWS ( 'Player Registrations' ), 'Date Table'[Year] = _prioryear )

VAR _previousyearscondition =

    CALCULATE ( COUNTROWS ( 'Player Registrations' ), 'Date Table'[Year] < _prioryear )

RETURN

    IF (

        HASONEVALUE ( 'Player Master'[Person ID] ) && HASONEVALUE ( 'Date Table'[Year] ),

        SWITCH (

            TRUE (),

            _prioryearcondition = 0

                && _previousyearscondition = 0

                && _eachyearcondition = 1, "New",

            _prioryearcondition = 1

                && _eachyearcondition = 1, "Re-committed",

            _prioryearcondition = 0

                && _previousyearscondition >= 1

                && _eachyearcondition = 1, "Return",

            "Not registered"

        )

    )

Thanks again 😀

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your message.

I am not sure how your desired outcome of a visualization looks like, but please check the below picture and the attached pbix file. I tried to create it by using my sample data.

All measures are in the attached pbix file.

[cwillems]

Hi Jihwan_Kim,

I tried using your PBIX file to recreate as I am trying to solve a similar problem for a sports club. See the table I got using your dax measure:

For your reference, this is my datamodel:

Here is some of the data I am working with (some fields are calculated columns):

I would like to be able to show when a player is playing their first season, when they have left. That way I will be able to show a retention %: players staying/total players that year. I will then show this percentage per age group, coach, etc. Do you have any imput on this? 

Thanks & appreciate your insights!

[RNZALR]

Thank you!  This is really helpful and I think it will give me exactly what I am looking for.  I may come back with a few other points to clarify, but so far it is proving to be exactly what I am looking for and I am very grateful!  😀