Forum Discussion

LurkingDude's avatar
LurkingDude
Frequent Visitor
1 year ago
Solved

Find Average Using Most Recent Date

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 structu...
  • FreemanZ's avatar
    FreemanZ
    1 year ago

    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

AntrikshSharma's avatar
AntrikshSharma
Icon for Community Champion rankCommunity Champion
1 year ago

LurkingDude 

For average you could do

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

 

LurkingDude's avatar
LurkingDude
Frequent Visitor
1 year ago

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.