Forum Discussion

sirgseymour's avatar
sirgseymour
Helper I
7 years ago
Solved

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

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?

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
    tarunsingla
    Solution Sage
    7 years ago

    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