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
Community Champion
1 year ago

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

LurkingDude's avatar
LurkingDude
Frequent Visitor
1 year ago

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

Thanks again.