Retrieve average of earliest and most recent values from one table based on date slicer

[blueandgold]

We have a single table of students with test scores.  Columns include createdon (the date the test is taken), testscore, and contactid.  We want to create a visual that displays the average first test score and the average most recent test score by all students, so that we can show improvements in test scores by students that take our course.  Here's a few conditions.

1. Each student included in the calculation must have taken the test at least twice, so we can calculate an average first time score and an average latest time score across all students who have taken the test at least twice during the date slicer range
2. We have a date slicer based on createdon date, so that if somone took the test once a month for 12 months in a row, but we have the date slicer start date be before their first test date and have the date slicer end date after their third test date but before their fourth, the test dates we want to compare are their first test and their third test
3. Since we're using the createdon value to determine the date of the test, it's actually a date/time field (I think, right?)

Here's some example data to help.

testid	contactid	createdon	testscore
1	A	January 2, 2021	60
2	B	January 2, 2021	87
3	C	January 2, 2021	70
4	A	February 1, 2021	68
5	A	February 3, 2021	96
6	B	February 5, 2021	80
7	B	February 15, 2021	99

So, if the date slicer for the page has start date of January 1, 2021 and an end date of February 14, 2021, we only want the following values to be in the calculation we run....

testid	contactid	createdon	testscore
1	A	January 2, 2021	60
2	B	January 2, 2021	87
5	A	February 3, 2021	96
6	B	February 5, 2021	80

....where we get the average of the earliest pairs of scores ((60+87)/2 = 73.5) and we get the average of the latest test scores ((96+80)/2 = 88) to be our sought after values.

Any hints or ideas on how I can get from here to there?  We're trying to do this with DirectQuery, but if we need to use Import, we can make the switch.  Thanks so much in advance!

[Greg_Deckler · Accepted solution]

@blueandgold Hmm. Well, since there is a date slicer involved probably better be a measure. Maybe something like the following:

Measure Min Date Average =
  VAR __Contacts = 
    SELECTCOLUMNS(
      FILTER(SUMMARIZE('Table',[contactid],"__Count",COUNTROWS('Table')),[__Count] > 1),
      "__contactid",[contactid]
    )
  VAR __Table = 
    ADDCOLUMNS(
      ADDCOLUMNS(
        SELECTCOLUMNS(
          FILTER('Table',[contactid] IN __Contacts),
          "__contactid",[contactid],
        ),
        "__Date" = MINX(FILTER('Table','Table'[contactid]=[__contactid]),[createdon])
      ),
      "__Score",MINX(FILTER('Table','Table'[contactid] = [__contactid] && [createdon] = [__Date]
    )
RETURN
  AVERAGEX(__Table,[__Score])


Measure Max Date Average =
  VAR __Contacts = 
    SELECTCOLUMNS(
      FILTER(SUMMARIZE('Table',[contactid],"__Count",COUNTROWS('Table')),[__Count] > 1),
      "__contactid",[contactid]
    )
  VAR __Table = 
    ADDCOLUMNS(
      ADDCOLUMNS(
        SELECTCOLUMNS(
          FILTER('Table',[contactid] IN __Contacts),
          "__contactid",[contactid],
        ),
        "__Date" = MAXX(FILTER('Table','Table'[contactid]=[__contactid]),[createdon])
      ),
      "__Score",MAXX(FILTER('Table','Table'[contactid] = [__contactid] && [createdon] = [__Date]
    )
RETURN
  AVERAGEX(__Table,[__Score])

[blueandgold]

@Greg_Deckler thank you so much for the detailed solution!  When implementing this, I'm getting the following error for the Measure Max Date Average:

Argument '4' in SELECTCOLUMNS function is required.

Any idea what I might be missing?

[Greg_Deckler]

@blueandgold Likely missing an ending ) somewhere. Perhaps try running the code through daxformatter.com to find the issue.

[blueandgold]

@Greg_Deckler that was it! This has worked beautifully, thank you so much!