Forum Discussion
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...
I have emailed you the file to the address you gave me last time. Thank you!
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 )
)
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?