Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |