Forum Discussion

banhngu's avatar
banhngu
Regular Visitor
5 months ago
Solved

Calculating Score Difference between oldest and newest score

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 col...
  • cengizhanarslan's avatar
    cengizhanarslan
    5 months ago

    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
  • mizan2390's avatar
    mizan2390
    5 months ago

    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's avatar
donbuser
Icon for Resolver I rankResolver I
5 months ago

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.