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