How to calculate Time spent in ONE column

[TomasPac]

Hello I have Time column with timed events, which are not consistent.
It's format is h/mm/ss (AM/PM)
It looks like this:
TIME                      STATE
8:05:15 AM          Moving
8:05:37 AM          Moving

8:05:58 AM          Stopped

8:06:03 AM          Stopped

14:25:03 PM        Moving

How can I calculate time spent while state is Moving and Stopped.

Something like:

Moving 2:45:10
Stopped 1:15:37

[v-juanli-msft]

Hi @TomasPac 

If ant of the answers solved your problem, could you kindly accept it as a solution to close this case and help the other members find it more quickly?

If not, please feel free to let me know.

Best Regards

Maggie

[BA_Pete]

Hi @TomasPac ,

I'm not at a computer so can't show example code, but the steps I would take are as follows. This is all done in Power Query:

1. Sort your datetime column ascending 

2. Add Index column starting from 1 (indexA)

3. Add Index column starting from 0 (indexB)

4. Left Outer Join the query on itself, using indexA as first join column, indexB for second join column

5. Expand datetime column from the merged result. This now gives you a 'datetime from' column (the original datetime column), and a 'datetime to' (the datetime from the next row brought up by the join) column.

6. Add a 'duration' column which will just be [datetime to] - [datetime from]

7. You can then use your duration column in measures within the report builder. You may want to also change the data format of the duration column in Power Query to decimal before you apply the changes, depending on how you want to use this result.

Hopefully this gives you a start.

Pete

[harshnathani]

Hi @TomasPac ,

Unable tounderstand the logic of your expectd output.

Can you pls explain with an example.

Reagrds,

HN

[ryan_mayu]

@TomasPac 

Could you please provide the detailed logic of calculation?

[TomasPac]

I need to calculate how much time is spent either while moving or stopped, by given data.

I have a time column wich shows what time was when event happened and what state was the object in.

So for example I want to check how long was the object moving yesterday so for yesterday from starting time till the end how long object was in Moving, or Stopped.