Forum Discussion

RNZALR's avatar
RNZALR
Helper I
3 years ago

Calculating Prior Year Values

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

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

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

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

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Super User
      3 years ago

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

        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?