Get the value of the previous ID dynamically based on sequence ID/ Rank ID

[EvanGetsItDone]

Hello, 
How can we be able to get the value of the previous ID dynamically?
I have values that are based on sequence ID or Rank ID. and I want to get the value of the previous ID of the selected ID.
Example (in this example, ID 4 is filtered out, so the expected "previous ID" should be ID 3):

This would be a big help If I can get this.

Thanks!

[hackcrr]

Hi, @EvanGetsItDone 

I used the data provided in your picture:

I used the following DAX expression to create a metric:

Previous ID =
VAR _table1 =
    CALCULATETABLE ( ALL ( 'Table4' ), 'Table4'[ID] <> 4 )
VAR _table =
    SUMMARIZE (
        _table1,
        Table4[ID],
        'Table4'[value],
        "prevous ID",
            CALCULATE (
                MAX ( 'Table4'[ID] ),
                FILTER ( ALL ( 'Table4' ), 'Table4'[ID] < SELECTEDVALUE ( 'Table4'[ID] ) ),
                REMOVEFILTERS ( 'Table4'[ID] )
            )
    )
VAR _seletedID =
    SELECTEDVALUE ( 'Table4'[ID] )
VAR _ID =
    MAXX ( FILTER ( _table, 'Table4'[ID] = _seletedID ), [prevous ID] )
RETURN
    IF ( _ID = 0, " ", IF ( _ID = 4, _ID - 1, _ID ) )

The result in table is as follows:

With this previous metric in place, I created a second metric using the following DAX expression:

Previous value =
VAR _curID = [Previous ID]
RETURN
    CALCULATE (
        MAX ( 'Table4'[value] ),
        FILTER ( ALL ( Table4 ), 'Table4'[ID] = _curID )
    )

The full results are shown below:

Best Regards,

hackcrr

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[EvanGetsItDone]

Hi! Thanks for spending time helping me figuring this out. however I don't specifically need the ID 4 to be filtered out since it would be based of a slicer that the user will choose, however in my post ID 4 is just an example. 

I need it to be dynamic, depending on what the user selects on the slicer on which one to filter out or not. 

Thanks tho! 

[v-yohua-msft · Accepted solution]

Hi, @EvanGetsItDone 

If you want to filter dynamically with slicers, you'll need to create a metric instead of the original ID. Start by creating two calculated columns to calculate the data in the previous row.

Previous ID = 
VAR CurrentID = 'Table'[ID]
VAR PreviousID = CALCULATE(MAX('Table'[ID]), FILTER('Table', 'Table'[ID] < CurrentID))
RETURN 
PreviousID

Previous Value = 
VAR CurrentID = 'Table'[ID]
VAR PreviousID = CALCULATE(MAX('Table'[ID]), FILTER('Table', 'Table'[ID] < CurrentID))
VAR PreviousValue = LOOKUPVALUE('Table'[Value], 'Table'[ID], PreviousID)
RETURN PreviousValue

Next, create a new table.

Then create a measure that when the ID is selected, the row data disappears

SelectID = 
VAR _slicer = MAX('Table 2'[ID])
RETURN
IF(MAX('Table'[ID])<>_slicer,MAX('Table'[ID]),BLANK())

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.