Retrieve Value from Previous Row in Filtered Table

[Whats_Cookin]

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!

[MFelix · Accepted solution]

Hi @Whats_Cookin,

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 )

[Whats_Cookin]

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.