Find Average Using Most Recent Date

[LurkingDude]

Hi, all.

New to DAX (but with some database background) so have been struggling to solve a simple issue. I have a data model that tracks tournament results for chess players. Partial table structure noted below:

dCalendar
   Date
   ...

dMembers
   MemberID
   Expiry
   ...

fResults
   ResultDate
   MemberID
   NewRating
   ...

dCalendar and fResults are related by Date and ResultDate; dMembers and fResults are related by MemberID. For each uique MemberID in dMembers, there can be one or hundreds of entries in the fResults table--just depends how active that player is.

I'm trying to calculate the average member rating using ONLY each member's MOST RECENT entry in the fResults table. I would like to restrict this calculation to only active members where dMembers[Expiry] is greater than today.

I'm sure this is pretty starightforward, but the max(date) bit is throwing me off. Many thanks for any suggestions.

[FreemanZ]

hi @LurkingDude ,

try like:

measure =

VAR _table =

ADDCOLUMNS(

    SUMMARIZE(

        fResults,

        dMembers[MemberID],

        dMembers[Expiry]

    ),

    "RecentDate",

    CALCULATE(MAX(fResults[ResultDate]))

)

VAR _result =

CALCULATE(

    AVERAGE(fResults[NewRating]),

    TREATAS(

       CALCULATETABLE(

            _table,

            dMembers[Expiry]>TODAY()

       ),

       dMembers[MemberID],

       dMembers[Expiry],

       fResults[ResultDate]

    )

)

RETURN _result

[AntrikshSharma]

@FreemanZ the _table variable inside CALCULATETABLE won't be filtered by TODAY.

[FreemanZ · Accepted solution]

hi @AntrikshSharma ,

yes, indeed. thank you very much for pointing this out. 

@LurkingDude 

try like this instead:

measure =

VAR _table =

ADDCOLUMNS(

    SUMMARIZE(

        CALCULATETABLE(

            fResults,

            dMembers[Expiry]>TODAY()

        ),

        dMembers[MemberID],

        dMembers[Expiry]

    ),

    "RecentDate",

    CALCULATE(MAX(fResults[ResultDate]))

)

VAR _result =

CALCULATE(

    AVERAGE(fResults[NewRating]),

    TREATAS(

        _table,

       dMembers[MemberID],

       dMembers[Expiry],

       fResults[ResultDate]

    )

)

RETURN _result

[LurkingDude]

Whoa! No wonder I couldn't figure it out. LOL.

Thanks for the response. I will try this out at first opportunity.

So I understand what the code is doing, will the code line CALCULATE(MAX(fResults[ResultDate])) iterate over _table and return the max date for EACH MemberID? I don't see where the grouping by MemberID is happening. Is it in the Summarize function? (I'm not too familiar with that one.)

Thanks for helping me understand.

[FreemanZ]

hi @LurkingDude ,

yes, done with SUMMARIZE.

the code is not verified, please come back and @me, in case of issue.

[AntrikshSharma]

@LurkingDude 

For average you could do

CALCULATE ( AVERAGE ( fResults[Rating] ), dMembers[Expiry] >= TODAY () )

[LurkingDude]

Thanks for the response. Yes, this will calculate the average rating for all non-expired members. But it does not take the most-recent rating only from the results table--this is the challenge I am having.

Each active member may have literally hundreds of entries in the fResults table, each entry with a rating. I need to calculate the average on ONLY each member's most-recent rating.

Thanks.

[AntrikshSharma]

@LurkingDude Define most recent, unless you have a column such as LastUpdateDate you can't quantify most recent.

[LurkingDude]

fResults[ResultDate] is the date of the most-recent rating update. This field is related to the Date field in dCalendar.

Thanks again.