Forum Discussion
Count time between rows
Probably not that difficult but still I am strugling.
Here is the issue:
I want to make a measure that shows me how many time each step took.
So second line would be 7:34 -/- 7:32 resulting in 2 minutes.
Etc.
6 Replies
rpinxt 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 - __PreviousThanks Greg_Deckler bit more difficult then I was expecting.
Tried it like this but no luck yet :
Time Spent =VAR __Current = MAX('Table 1'[Time])VAR __PreviousDate = MAXX(FILTER('Table 1','Table 1'[Time] < EARLIER(MAX('Table 1'[Time]),1)VAR __Previous = MAXX(FILTER('Table 1','Table 1'[Time]=__PreviousDate),[Value])RETURN__Current - __PreviousBut it starts to go wrong in the EARLIER part....rpinxt Can you post your data as text?
Greg_Deckler you mean like this?
Date Name Time Process 01-02-24 ALHAJJX 07:32:47 Sumtotal 01-02-24 ALHAJJX 07:34:54 Apply 01-02-24 ALHAJJX 10:32:36 Pauze Uit 01-02-24 ALHAJJX 10:51:13 Pauze Uit 01-02-24 ALHAJJX 11:24:17 Apply 01-02-24 ALHAJJX 12:30:54 Pauze In 01-02-24 ALHAJJX 13:05:07 Pauze Uit 01-02-24 ALHAJJX 15:01:02 Pauze In 01-02-24 ALHAJJX 15:19:09 Pauze Uit 01-02-24 ALHAJJX 15:36:23 Apply 01-02-24 ALHAJJX 15:57:11 Stop
