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

FreemanZ's avatar
FreemanZ
Super User
1 year ago

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

  • 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.

  • 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