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

mizan2390's avatar
mizan2390
Icon for Super User rankSuper User
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