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

LurkingDude's avatar
LurkingDude
Frequent Visitor
1 year ago

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's avatar
FreemanZ
Super User
1 year ago

hi LurkingDude ,

 

yes, done with SUMMARIZE.

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