Forum Discussion
Retrieve Value from Previous Row in Filtered Table
Hi Everyone,
I am having trouble accessing the previous row in a filtered table. I'd like to access the previous row so that I can calculate the difference in the date/time between the a value in the current row and the value of another column in the previous row. I've tried using an index but applying filters cause it to become non-sequential resulting in the wrong records being pulled. I also tried to make a variable index but my previous row measures either say it can't be used or it results in a circular dependency. I think what I'm looking for is something along the lines of determining the 'nearest but lower' index or date/time compared to the current row.
Here is a simplified example of the data:
| index | start_time | end_time | route | start_loc | end_loc |
| 1 | 5/26/2023 18:59 | 5/26/2023 19:22 | apple_orange | apple | orange |
| 2 | 5/26/2023 19:25 | 5/26/2023 20:35 | apple_banana | apple | banana |
| 3 | 5/26/2023 19:34 | 5/26/2023 19:57 | banana_orange | banana | orange |
| 4 | 5/26/2023 20:51 | 5/26/2023 20:56 | orange_apple | orange | apple |
| 5 | 5/26/2023 20:52 | 5/26/2023 21:27 | orange_banana | orange | banana |
| 6 | 5/26/2023 21:13 | 5/26/2023 22:05 | banana_orange | banana | orange |
| 7 | 5/26/2023 21:39 | 5/26/2023 21:51 | banana_apple | banana | apple |
I am currently using a slicer with a search bar to find Routes that match positions. For example if I want to see all the incoming and outgoing transactions for 'apple', I type apple into the search bar of the Route slicer and it only gives me transactions involving 'apple'. After this filter is applied, I would like to retrieve the 'end_time' of the previous row and subtract it from the current row's 'start_time' to get the 'service_delay'. Ultimately I would like to achieve something like the table below (minus the previous_end_time column, it's just for explanation purposes. Also not picky about the service delay formatting, as long as its easily readable):
| index | start_time | end_time | previous_end_time | service_delay | route | start_loc | end_loc |
| 1 | 5/26/2023 18:59 | 5/26/2023 19:22 | apple_orange | apple | orange | ||
| 2 | 5/26/2023 19:25 | 5/26/2023 20:35 | 5/26/2023 19:22 | 3 | apple_banana | apple | banana |
| 4 | 5/26/2023 20:51 | 5/26/2023 20:56 | 5/26/2023 20:35 | 16 | orange_apple | orange | apple |
| 7 | 5/26/2023 21:39 | 5/26/2023 21:51 | 5/26/2023 20:56 | 43 | banana_apple | banana | apple |
Thanks for your time and I appreciate any help or advice you can share!
Hi Anonymous,
Try the following measure:
Service_Delay = VAR previoustime = MAXX ( TOPN ( 1, FILTER ( ALLSELECTED ( 'Routes'[index], 'Routes'[start_time], 'Routes'[end_time], 'Routes'[route] ), 'Routes'[index] < SELECTEDVALUE ( 'Routes'[index] ) ), 'Routes'[start_time], DESC ), 'Routes'[end_time] ) RETURN DATEDIFF ( previoustime, SELECTEDVALUE ( 'Routes'[start_time] ), MINUTE )
2 Replies
Hi Anonymous,
Try the following measure:
Service_Delay = VAR previoustime = MAXX ( TOPN ( 1, FILTER ( ALLSELECTED ( 'Routes'[index], 'Routes'[start_time], 'Routes'[end_time], 'Routes'[route] ), 'Routes'[index] < SELECTEDVALUE ( 'Routes'[index] ) ), 'Routes'[start_time], DESC ), 'Routes'[end_time] ) RETURN DATEDIFF ( previoustime, SELECTEDVALUE ( 'Routes'[start_time] ), MINUTE )Hey MFelix,
Thanks for the help! The measure works well when the table is filtered so thank you for the solution! 🙂
For any future readers, I sorted my data by start_time and added an index column. The data was originally indexed from a creation time but I presented it here as if it was off the start time, no biggie. Also, when the data is not filtered by a route, the measure returns mostly correct data, except for the first 5 rows. My data won't be used in this format, it'll likely always be filtered but it's something to look out for if you are modifying it for your own purposes.