Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
RNZALR
Helper I
Helper I

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 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. 

RNZALR_0-1673581681659.png

RNZALR_2-1673581792610.png

 

 

1 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.

 

Jihwan_Kim_0-1673595917029.png

 

 

 

All measures are in the attached pbix file.

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

5 REPLIES 5
Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_0-1673584244964.png

 

Jihwan_Kim_1-1673584864697.png

 

 

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"
        )
    )

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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 😀

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.

 

Jihwan_Kim_0-1673595917029.png

 

 

 

All measures are in the attached pbix file.

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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:

 

cwillems_0-1674091190027.png

 

For your reference, this is my datamodel:

cwillems_1-1674091238781.png

 

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

cwillems_2-1674091414537.png

 

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!

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors