Forum Discussion
DAX measure for comparing rows and time difference
Hi,
I'm trying to build a measure (called Time Diff) that compares two consecutive rows, in this case the rows in the Time column. As shown in the column on the far right, I want the output to be in seconds, i.e. between row 1 and 2 it's 36 seconds, between row 2 and row 3 it's 53 seconds and so on.
If possible, I want to achieve this result by using a measure, not a calculated column. I've read about how to solve similar problems by creating a calcualted column using the EARLIER function.
Thanks in advance!
/Ella
Anonymous create this measure:
Time Diff = VAR _current_time = SELECTEDVALUE('Table'[Time]) VAR _previous_time = MAXX( FILTER( ALL('Table'), 'Table'[Time] < _current_time ), 'Table'[Time] ) VAR _result = (_current_time - _previous_time) * 86400 RETURN IF( NOT ISBLANK(_previous_time), _result )
5 Replies
- SpartaBI
Community Champion
Anonymous create this measure:
Time Diff = VAR _current_time = SELECTEDVALUE('Table'[Time]) VAR _previous_time = MAXX( FILTER( ALL('Table'), 'Table'[Time] < _current_time ), 'Table'[Time] ) VAR _result = (_current_time - _previous_time) * 86400 RETURN IF( NOT ISBLANK(_previous_time), _result )Solved it! Just replaced the ALL function with ALLSELECTED. Thanks for your help SpartaBI 🙂
- Greg_Deckler
Anonymous See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous
