topic Re: Calculating Score Difference between oldest and newest score in DAX Commands and Tips

Calculating Score Difference between oldest and newest score

banhngu — Fri, 03 Apr 2026 15:59:55 GMT

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.

Re: Calculating Score Difference between oldest and newest score

Hans-Georg_Puls — Fri, 03 Apr 2026 18:09:43 GMT

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.

Re: Calculating Score Difference between oldest and newest score

cengizhanarslan — Sat, 04 Apr 2026 11:35:29 GMT

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

Re: Calculating Score Difference between oldest and newest score

donbuser — Sun, 05 Apr 2026 13:33:13 GMT

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.

Re: Calculating Score Difference between oldest and newest score

mizan2390 — Mon, 06 Apr 2026 06:57:03 GMT

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

Re: Calculating Score Difference between oldest and newest score

v-kpoloju-msft — Fri, 10 Apr 2026 06:36:08 GMT

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.

Re: Calculating Score Difference between oldest and newest score

banhngu — Fri, 10 Apr 2026 13:27:48 GMT

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

Re: Calculating Score Difference between oldest and newest score

v-kpoloju-msft — Mon, 13 Apr 2026 05:29:00 GMT

Hi banhngu,

Thanks for confirming, glad to hear the responses helped and that you were able to identify a solution.

If you run into any further questions or need additional clarification, feel free to reach out happy to assist anytime.

Re: Calculating Score Difference between oldest and newest score

v-kpoloju-msft — Fri, 17 Apr 2026 09:56:55 GMT

Hi banhngu,

Just wanted to follow up. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.

Thank you.