Finding the difference between values on two dates the first being hte most current and the second

[sirgseymour]

Hello,

I have a dataset that has a value column with an associated Timestamp column. The timestamps are not uniform so the time between any two time stamps is different. What I am trying to do is to find the difference between the value associated with the current time stamp and the value at the next most recent time stamp. 

Table1

Value	TimeStamp
500	30-05-19 10:02
129	30-05-19 11:10
178	31-05-19 12:20
168	31-05-19 19:45
201	01-06-19 20:01
1235	02-06-19 19:42
1352	03-06-19 19:43

Is Latest Date Column = MAX(Table1[TimeStamp])

Last Date = MAX(Table1[Is Latest Date Column])

Second Last Day Column = calculate(max(Table1[TimeStamp]),filter(Table1,(Table1[TimeStamp])<(Table1[Is Latest Date Column])))

Second Last Date = MAX(Table1[Second Last Day Column])

Current Value = CALCULATE(MAX(Value[Value),filter(Table1,Table1[TimeStamp]=MAX(Value[TimeStamp])))

Previous Value = CALCULATE(MAX(Table1 [Value]),filter(Table1,MAX(Table1 [TimeStamp])< Table1 [Is Latest Date Column]))

Difference = calculate(Table1 [Current Value]- Table1 [Previous Value])

The Current Value being returned is correct but the Previous Value value being returned is the maximum of the value list not the filtered value

Any ideas?

[Anonymous · Accepted solution]

Hi @sirgseymour ,

You can try to use following measure to calculate diff between current and previous value:

Measure =
VAR _currDate =
    MAX ( Table[TimeStamp] )
VAR _previous =
    CALCULATE (
        MAX ( Table[TimeStamp] ),
        FILTER ( ALLSELECTED ( Table ), [TimeStamp] < _currDate )
    )
RETURN
    MAX ( Table[Value] ) - LOOKUPVALUE ( Table[Value], Table[TimeStamp], _previous )

Regards,

Xiaoxin Sheng

[tarunsingla]

You would have to use the EARLIER function to get the previous timestamp.

See this file for the reference: EARLIER Example

Hope this helps.

Regards,

Tarun