Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 ...
Need Help
  • MFelix's avatar
    MFelix
    3 years ago

    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 )

     

MFelix's avatar
MFelix
Super User
3 years ago

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 )

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.