Calculating Prior Year Values

[RNZALR]

I've got some player data for a sport and I've calculated the number of players who are either new, returning or retained.  The definitions of each type is:

• New:  Does not appear in the database in any previous season
• Retained:  Played last season
• Returning:  Did not play last season but has played in the past and has now registered to play in the current season

My measures to calcuate each are working fine, however I am struggling to calculate the prior year metrics.  Here is the table I have:

I want the prior year numbers to appear on the table as well but I am struggling with the dax to make that happen.  For example, in the 2023 season, I want the Prior Year New Player Number to show 2,933 and the Prior Returning Player number to show 1,460.

How would I amend my dax formula to make that work?  Here are the formulas for the current years:

New Player Numbers =

VAR _currentyear =

    MAX ( 'Date Table'[Season] )

VAR _registerlist =

    SUMMARIZE ( 'Player Registrations', 'Player Master'[Person ID] )

VAR _previousregisterlist =

    CALCULATETABLE (

        SUMMARIZE ( 'Player Registrations', 'Player Registrations'[Person ID] ),

        'Date Table'[Season]< _currentyear

    )

RETURN

    COUNTROWS ( EXCEPT ( _registerlist, _previousregisterlist ) )

and

Retained Player Numbers =

VAR _currentyear =

    MAX ( 'Date Table'[Season] )

VAR _registerlist =

    SUMMARIZE ( 'Player Registrations', 'Player Master'[Person ID] )

VAR _prioryeargisterlist =

    CALCULATETABLE (

        SUMMARIZE ( 'Player Registrations', 'Player Master'[Person ID] ),

        'Date Table'[Season] = _currentyear - 1

    )

RETURN

    COUNTROWS ( INTERSECT ( _registerlist, _prioryeargisterlist ) )

@Jihwan_Kim you were really helpful the last time on this one.  Any thoughts on this?

[Jihwan_Kim]

Hi,

I tried to create a DAX formula based on what I got from the question.
Please check if it suits your datamodel.

Prior year New Player Numbers =
VAR _currentyear =
    MAX ( 'Date Table'[Season] )
VAR _prioryear = _currentyear - 1
VAR _prioryearregisterlist =
    CALCULATETABLE (
        SUMMARIZE ( 'Player Registrations', 'Player Registrations'[Person ID] ),
        'Date Table'[Season] = _prioryear
    )
VAR _previousofprioryearregisterlist =
    CALCULATETABLE (
        SUMMARIZE ( 'Player Registrations', 'Player Registrations'[Person ID] ),
        'Date Table'[Season] < _prioryear
    )
RETURN
    COUNTROWS (
        EXCEPT ( _prioryearregisterlist, _previousofprioryearregisterlist )
    )

[RNZALR]

It is close, and certainly better than my attempts, but the numbers are not lining up.  See the table below

What would the Prior Year Returning Player formula look like as well?

[Jihwan_Kim]

Hi,

Thank you for your message.

Please provide a link of the sample pbix file, and then I can try to look into it to come up with a more accurate solution.

Thanks.

[RNZALR]

I have emailed you the file to the address you gave me last time.  Thank you!

[Jihwan_Kim]

Hi,

Thank you.

I am not sure which one is the desired output, but please check the below.

In each measure, two different columns from two different tables are used in the comparison.

Player Registrations[Person ID] or Player Master[Person ID] ???

fix the [New Player Numbers] measure,

New Player Numbers = 
VAR _currentyear =
    MAX ( 'Date Table'[Season] )
VAR _registerlist =
    SUMMARIZE ( 'Player Registrations', 'Player Registrations'[Person ID] )
VAR _previousregisterlist =
    CALCULATETABLE (
        SUMMARIZE ( 'Player Registrations', 'Player Registrations'[Person ID] ),
        'Date Table'[Season]< _currentyear
    )
RETURN
    COUNTROWS ( EXCEPT ( _registerlist, _previousregisterlist ) )

or, 

fix the [Prior year New Player Numbers] measure.

Prior year New Player Numbers = 
VAR _currentyear =
    MAX ( 'Date Table'[Season] )
VAR _prioryear = _currentyear - 1
VAR _prioryearregisterlist =
    CALCULATETABLE (
        SUMMARIZE ( 'Player Registrations', 'Player Master'[Person ID] ),
        'Date Table'[Season] = _prioryear
    )
VAR _previousofprioryearregisterlist =
    CALCULATETABLE (
        SUMMARIZE ( 'Player Registrations', 'Player Registrations'[Person ID] ),
        'Date Table'[Season] < _prioryear
    )
RETURN
    COUNTROWS (
        EXCEPT ( _prioryearregisterlist, _previousofprioryearregisterlist )
    )

[RNZALR]

Thank you!

The second of your measures, the Prior Year New Player Numbers is what I was wanting to get!

What would the measures for Prior Year Returning Players and Prior Year Retained Players look like?