Forum Discussion

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 column called "Point Increase."

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

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

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

8 Replies

Hans-Georg_Puls
Super User
5 months ago
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.

cengizhanarslan

Super User

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

donbuser
Resolver 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.

mizan2390

Super User

5 months ago

hi banhngu

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

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

v-kpoloju-msft
Community Support
5 months ago
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
  Regular Visitor
  5 months ago
  Yes, the responses helped. I marked one of them as the solution.
  - v-kpoloju-msft
    Community Support
    4 months ago
    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.

Forum Discussion

Calculating Score Difference between oldest and newest score

8 Replies

Related content

Dynamic Calculation Different Period

Different Calculations

Calculating Differences

How to add two different calculated columns of two different table

Calculating day/time difference