topic Re: Find Average Using Most Recent Date in DAX Commands and Tips

Find Average Using Most Recent Date

LurkingDude — Wed, 13 Nov 2024 18:51:38 GMT

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.

Re: Find Average Using Most Recent Date

AntrikshSharma — Wed, 13 Nov 2024 19:25:48 GMT

LurkingDude

For average you could do

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

Re: Find Average Using Most Recent Date

LurkingDude — Wed, 13 Nov 2024 19:40:12 GMT

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.

Re: Find Average Using Most Recent Date

AntrikshSharma — Wed, 13 Nov 2024 19:53:36 GMT

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

Re: Find Average Using Most Recent Date

LurkingDude — Wed, 13 Nov 2024 19:58:00 GMT

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

Thanks again.

Re: Find Average Using Most Recent Date

FreemanZ — Wed, 13 Nov 2024 20:21:00 GMT

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

Re: Find Average Using Most Recent Date

LurkingDude — Wed, 13 Nov 2024 20:31:10 GMT

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.

Re: Find Average Using Most Recent Date

FreemanZ — Wed, 13 Nov 2024 20:41:11 GMT

hi LurkingDude ,

yes, done with SUMMARIZE.

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

Re: Find Average Using Most Recent Date

AntrikshSharma — Thu, 14 Nov 2024 05:27:13 GMT

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

Re: Find Average Using Most Recent Date

FreemanZ — Thu, 14 Nov 2024 05:38:55 GMT

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