Forum Discussion

RNZALR's avatar
RNZALR
Helper I
3 years ago
Solved

Player Retention Rates

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 reg...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    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.

     

Jihwan_Kim's avatar
Jihwan_Kim
Super User
3 years ago

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's avatar
RNZALR
Helper I
3 years ago

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's avatar
    Jihwan_Kim
    Super User
    3 years ago

    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.

     

    • RNZALR's avatar
      RNZALR
      Helper I
      3 years ago

      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!  😀

    • cwillems's avatar
      cwillems
      New Member
      3 years ago

      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!