Finding the change of a column value since last non-unique date

[Paldren]

I have a table that looks a lot like this:

[TimeStamp]	[ProjectID]	[Score]
01/15/2022	PRJ01	10
01/15/2022	PRJ02	1000
01/15/2022	PRJ03	1
01/14/2022	PRJ01	14
01/14/2022	PRJ02	999
01/14/2022	PRJ03	2
01/13/2022	PRJ01	8
01/13/2022	PRJ02	989
01/13/2022	PRJ03	1

I want to calculate the change in [Score] of [ProjectID] from previous [TimeStamp] and put it into a custom column like this:

[Change Since Previous Entry]
4
1
-1
6
10
1
<blank>
<blank>
<blank>

But I am at a complete loss on where to start.

[Jos_Woolley · Accepted solution]

Hi,

Assuming your table is named 'MyTable', this Calculated Column:

Column =
VAR TimeStamp = MyTable[TimeStamp]
VAR ProjectID = MyTable[ProjectID]
VAR PreviousTimeStamp =
    CALCULATE(
        MAX( MyTable[TimeStamp] ),
        FILTER(
            MyTable,
            MyTable[ProjectID] = ProjectID
                && MyTable[TimeStamp] < TimeStamp
        )
    )
RETURN
    IF(
        ISBLANK( PreviousTimeStamp ),
        BLANK(),
        MyTable[Score]
            - CALCULATE(
                MIN( MyTable[Score] ),
                FILTER(
                    MyTable,
                    MyTable[ProjectID] = ProjectID
                        && MyTable[TimeStamp] = PreviousTimeStamp
                )
            )
    )

Regards

[Paldren]

Thank you. I was lost with the calculation of the PreviousTimestamp. Was trying to find a way to do it by index of a DISCRETE([TimeStamp]) and that obviously doesn't work.