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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
RNZALR
Helper I
Helper I

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

RNZALR_0-1683686234813.png

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?

6 REPLIES 6
Jihwan_Kim
Super User
Super User

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

 

 

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


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

RNZALR_0-1683688683587.png

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

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.

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


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

 

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


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?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors