Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Whats_Cookin
Regular Visitor

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:

indexstart_timeend_timeroutestart_locend_loc
15/26/2023 18:595/26/2023 19:22apple_orangeappleorange
25/26/2023 19:255/26/2023 20:35apple_bananaapplebanana
35/26/2023 19:345/26/2023 19:57banana_orangebananaorange
45/26/2023 20:515/26/2023 20:56orange_appleorangeapple
55/26/2023 20:525/26/2023 21:27orange_bananaorangebanana
65/26/2023 21:135/26/2023 22:05banana_orangebananaorange
75/26/2023 21:395/26/2023 21:51banana_applebananaapple

 

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):

indexstart_timeend_timeprevious_end_timeservice_delayroutestart_locend_loc
15/26/2023 18:595/26/2023 19:22  apple_orangeappleorange
25/26/2023 19:255/26/2023 20:355/26/2023 19:223apple_bananaapplebanana
45/26/2023 20:515/26/2023 20:565/26/2023 20:3516orange_appleorangeapple
75/26/2023 21:395/26/2023 21:515/26/2023 20:5643banana_applebananaapple

 

Thanks for your time and I appreciate any help or advice you can share!

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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 )

MFelix_0-1686782617184.pngMFelix_1-1686782637040.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
MFelix
Super User
Super User

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 )

MFelix_0-1686782617184.pngMFelix_1-1686782637040.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors