Complex Row Time Difference

[Anonymous]

Hi Team. Is there a way to reproduce this in Powerbi.?

This is the table I have to, in theory, I would like to subtract the time or duration between 2:36 PM and 2:50PM the same way I did in my excel file. Any idea on how to achieve this?

Thanks

[Ashish_Mathur]

Hi,

Try this calculated column formula

=data[start time]-calculate(max(data[end time]),filter(data,data[start time]<earlier(data[start time])))

Hope this helps.

[v-henryk-mstf]

Hi @Anonymous ,

According to the table you provided, your purpose is to get the difference between the Time Out column and the next time value in Time IN column through power Bi. I did the following test: by defining two variables, var curindex is used to obtain the index value, var nextstratime is used to obtain the date value of the next row of the current row in the Start Time column, and finally the difference value is obtained by using the DATEDIFF function.

Here is the sample pbix file.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I cant download the file, can you grant me access?

[v-henryk-mstf]

There @carlosdajer ,

I am sorry for my behavior.

Here is the right sample pbix file.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Can you do the same but with unsorted rows? I have the index but the timestamp is not in order. How can we achieve the same result?

[v-henryk-mstf]

Hi @Anonymous，

The created Standard answer measure calculates the result of the current row. When the timestamp changes, the value will also change. And the order of calculation will be based on index column, you can observe "var st". In addition, you can refer to the Duration measure, even if it is an unsorted row, it will still be calculated based on the index.

You can check the pbix file.

Best regards,

Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for the help. Appreciate the time. m not getting the same results. Im sharing the code + the table 

.Duration =

var curindex = MAX('OMNI Hours Report'[Index])

var nextstartime = CALCULATE(MIN('OMNI Hours Report'[Start Time]),FILTER(ALL('OMNI Hours Report'),'OMNI Hours Report'[Index]>=curindex+1))

return DATEDIFF(MAX('OMNI Hours Report'[End Time]),nextstartime,SECOND)

[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

[Anonymous]

That did not work. Sorry