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 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
Hi @TomasPac
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
Proud to be a Datanaut!
Hi @TomasPac ,
Unable tounderstand the logic of your expectd output.
Can you pls explain with an example.
Reagrds,
HN
Could you please provide the detailed logic of calculation?
Proud to be a Super User!
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.
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 |
---|---|
113 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |