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

 

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

 

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