Calculating Score Difference between oldest and newest score

[banhngu]

What can I write to calculate the difference between a person's earliest score and latest score based on name and date? I was trying to create a separate column for that calculation under a column called "Point Increase."

Using Bob as an example, his point increase column would display 10. I'm trying to calculate for all students.

[v-kpoloju-msft]

Hi @banhngu, 

Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to @mizan2390, @donbuser, @cengizhanarslan, @Hans-Georg_Puls,  for those inputs on this thread.

Has your issue been resolved? If the response provided by the community member @mizan2390, @donbuser, @cengizhanarslan, @Hans-Georg_Puls, addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

Thank you for using the Microsoft Community Forum.

[banhngu]

Yes, the responses helped. I marked one of them as the solution.

[mizan2390]

hi @banhngu 

To calculate the point increase between a student's earliest and latest composite scores, you can use Variables (VAR) combined with the CALCULATE function to safely navigate the dates for each student.

Because you mentioned adding this as a separate column, I will provide the DAX for a Calculated Column, but I will also provide the Measure approach, which is usually recommended over calculated columns for dynamic reporting.

Point Increase = 
// 1. Identify the earliest and latest dates for this specific student
VAR EarliestDate = 
    CALCULATE (
        MIN ( TestScores[date_taken] ),
        ALLEXCEPT ( TestScores, TestScores[student_name] )
    )

VAR LatestDate = 
    CALCULATE (
        MAX ( TestScores[date_taken] ),
        ALLEXCEPT ( TestScores, TestScores[student_name] )
    )

// 2. Retrieve the Composite Score on the Earliest Date
VAR EarliestScore = 
    CALCULATE (
        MAX ( TestScores[Composite Score] ), 
        ALLEXCEPT ( TestScores, TestScores[student_name] ),
        TestScores[date_taken] = EarliestDate
    )

// 3. Retrieve the Composite Score on the Latest Date
VAR LatestScore = 
    CALCULATE (
        MAX ( TestScores[Composite Score] ), 
        ALLEXCEPT ( TestScores, TestScores[student_name] ),
        TestScores[date_taken] = LatestDate
    )

// 4. Calculate the difference
RETURN
    LatestScore - EarliestScore

If you place student_name into a Table or Matrix visual in your report, a Measure is much more efficient. A measure does not consume memory in your data model like a calculated column does. Because the visual itself automatically filters down to the specific student, you do not need the ALLEXCEPT function here.

Point Increase Measure = 
VAR EarliestDate = MIN ( TestScores[date_taken] )
VAR LatestDate = MAX ( TestScores[date_taken] )

VAR EarliestScore = 
    CALCULATE (
        MAX ( TestScores[Composite Score] ),
        TestScores[date_taken] = EarliestDate
    )

VAR LatestScore = 
    CALCULATE (
        MAX ( TestScores[Composite Score] ),
        TestScores[date_taken] = LatestDate
    )

RETURN
    IF (
        NOT ISBLANK(EarliestScore) && NOT ISBLANK(LatestScore),
        LatestScore - EarliestScore
    )

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

[donbuser]

This is a great use case for working with row context vs. filter context — you basically want to compare each person’s first and last recorded score.

If you’re doing this as a calculated column, one approach is to anchor both the earliest and latest score for each person, then take the difference:

Point Increase =
VAR FirstScore =
CALCULATE(
MIN('Table'[Score]),
FILTER(
'Table',
'Table'[Name] = EARLIER('Table'[Name])
&& 'Table'[Date] =
CALCULATE(
MIN('Table'[Date]),
FILTER('Table', 'Table'[Name] = EARLIER('Table'[Name]))
)
)
)
VAR LastScore =
CALCULATE(
MAX('Table'[Score]),
FILTER(
'Table',
'Table'[Name] = EARLIER('Table'[Name])
&& 'Table'[Date] =
CALCULATE(
MAX('Table'[Date]),
FILTER('Table', 'Table'[Name] = EARLIER('Table'[Name]))
)
)
)
RETURN
LastScore - FirstScore

This will return the same “point increase” for each row per person, based on their earliest and latest dates.

Alternatively, if you don’t specifically need it as a column, this is often cleaner as a measure using MIN/MAX date context, especially if you’re visualizing it.

I’ve run into similar scenarios where the tricky part isn’t the math itself, but making sure you’re consistently anchoring to the correct first/last record per entity.

[cengizhanarslan]

Please try the measure below:

Point Increase =
VAR _Student     = SELECTEDVALUE ( 'Table'[student_name] )
VAR _EarliestDate =
    CALCULATE (
        MIN ( 'Table'[date_taken] ),
        'Table'[student_name] = _Student
    )
VAR _LatestDate =
    CALCULATE (
        MAX ( 'Table'[date_taken] ),
        'Table'[student_name] = _Student
    )
VAR _EarliestScore =
    CALCULATE (
        MAX ( 'Table'[Composite Score] ),
        'Table'[student_name] = _Student,
        'Table'[date_taken] = _EarliestDate
    )
VAR _LatestScore =
    CALCULATE (
        MAX ( 'Table'[Composite Score] ),
        'Table'[student_name] = _Student,
        'Table'[date_taken] = _LatestDate
    )
RETURN
    _LatestScore - _EarliestScore

If you strictly need calculated column, you could try using:

Point Increase =
VAR _Student      = 'Table'[student_name]
VAR _EarliestDate = CALCULATE ( MIN ( 'Table'[date_taken] ), ALL ( 'Table' ), 'Table'[student_name] = _Student )
VAR _LatestDate   = CALCULATE ( MAX ( 'Table'[date_taken] ), ALL ( 'Table' ), 'Table'[student_name] = _Student )
VAR _EarliestScore =
    CALCULATE (
        MAX ( 'Table'[Composite Score] ),
        ALL ( 'Table' ),
        'Table'[student_name] = _Student,
        'Table'[date_taken]   = _EarliestDate
    )
VAR _LatestScore =
    CALCULATE (
        MAX ( 'Table'[Composite Score] ),
        ALL ( 'Table' ),
        'Table'[student_name] = _Student,
        'Table'[date_taken]   = _LatestDate
    )
RETURN
    _LatestScore - _EarliestScore

[Hans-Georg_Puls]

Hi @banhngu ,

try a measure like this one that doesn't care about the names:

Score Difference =

VAR _EarliestDate = MIN(Scores[date.taken])

VAR _LatestDate = MAX(Scores[date.taken])

VAR _EarliestScore = CALCULATE(MIN(Scores[Composite Score]), Scores[date.taken] = _EarliestDate)

VAR _LatestScore = CALCULATE(MIN(Scores[Composite Score]), Scores[date.taken] = _LatestDate)

RETURN

    _LatestScore - _EarliestScore

Based on this measure add a matrix or table visual to your report with student_name as rows resp. first column and the measure as values resp. next column. Every row sets a filter for student_name, the measure is calculated based on that filter setting.

I added some data for a Lisa to your demo set of data:

Hope that helps.