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 all,
I would like to ask for help with calculation of time difference between specific rows in one table. I have this kind of data:
DATE/TIME | RC | KEY |
7-10-20 6:00 AM | 1101 | 01 |
7-10-20 6:05 AM | 1102 | 01 |
7-10-20 6:10 AM | 1103 | 01 |
7-10-20 6:20 AM | 1101 | 04 |
7-10-20 6:25 AM | 1102 | 05 |
7-10-20 6:30 AM | 1103 | 06 |
7-10-20 6:30 AM | 1101 | 03 |
7-10-20 6:40 AM | 1102 | 03 |
7-10-20 6:50 AM | 1103 | 03 |
And what I want to do is to calculate time difference between rows with key - 04, 05, 06 and next row but with the same RC number. So my expected output is:
DATE/TIME | RC | KEY | TIME DIFFERENCE |
7-10-20 6:00 AM | 1101 | 01 | |
7-10-20 6:05 AM | 1102 | 01 | |
7-10-20 6:10 AM | 1103 | 01 | |
7-10-20 6:20 AM | 1101 | 04 | 0:10 |
7-10-20 6:25 AM | 1102 | 05 | 0:15 |
7-10-20 6:30 AM | 1103 | 06 | 0:20 |
7-10-20 6:30 AM | 1101 | 03 | |
7-10-20 6:40 AM | 1102 | 03 | |
7-10-20 6:50 AM | 1103 | 03 |
* colors marked which rows are calculated
Thank you for your help in advance.
P
Solved! Go to Solution.
Finally I found solution. For those whom it can help:
Finally I found solution. For those whom it can help:
@Peter_2020 , Try a new column like
if([Key] in {"04","05","06"}, [Date/time], Maxx(filter(table,[RC] =earlier([RC]) && [Date/time] <earlier([Date/time])),[Date/time]), blank())
But I doubt you are doing diff with the same RC number
@amitchandak Thanks for your help but it doesn´t work. I forgot to mention that I am using DirectQuery mode.
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 |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |