Difference between exam scores by multiple dates

[rishtin]

Hello,

I have a table with StudentID, Score and TestDate columns. I am trying to identify the difference in scores between two dates. When there are only two TestDates for a student then I can manage that, however when there are more than 2 then there's a problem with my dax.

Here's what i am trying to achieve;

StudentID, Score, TestDate, DifferenceinScore
stud001, 90, 2019-01-15, blank

stud001, 95, 2019-01-20, 5

stud001, 96, 2019-02-15, 1

How could i do this in a calculated column?
thanks

[jdbuchanan71 · Accepted solution]

Hello @rishtin 

Give this a try.

Score Change = 
VAR CurrStudent = 'Table'[StudentID]
VAR CurrDate = 'Table'[TestDate]
VAR PrevTestDate = 
CALCULATE( 
    MAX ( 'Table'[TestDate] ), 
    ALL ( 'Table' ), 
    'Table'[StudentID] = CurrStudent, 
    'Table'[TestDate] < CurrDate
)
RETURN
IF ( 
    NOT ISBLANK ( PrevTestDate ),
    'Table'[Score] - 
    CALCULATE( 
        MAX ( 'Table'[Score] ), 
        ALL( 'Table' ), 
        'Table'[StudentID] = CurrStudent, 
        'Table'[TestDate] = PrevTestDate 
    )
)

[rishtin]

Hi @jdbuchanan71 

It worked perfectly.

Highly appreciate this.