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