Get the 2nd to the Latest/Last Value of a column

[EvanGetsItDone]

Hey, 
So I wanted to get the 2nd latest/last value of a column, say for example:

ID	Name	Value
1	Test1	0
2	Test2	69
3	Test3	42

in this scenario, I need to get the 2nd to the last row which is 69. 

how can i get this in a measure or column?

Thanks yall, much appreciated.

[Anonymous]

Hi @EvanGetsItDone ,

You can create a measure as below to get it, please find the details in the attachment.

2nd latest value = 
VAR _maxid =
    CALCULATE ( MAX ( 'Table'[ID] ), ALLSELECTED ( 'Table' ) )
VAR _secondmid =
    CALCULATE (
        MAX ( 'Table'[ID] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[ID] < _maxid )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( 'Table', 'Table'[ID] = _secondmid )
    )

Best Regards

[Idrissshatila]

Hello @EvanGetsItDone ,

try the following

2nd Last Value = CALCULATE(MAX('Table'[Value]),'Table'[ID] = MAX('Table'[ID])-1)

[EvanGetsItDone]

Hello Thanks!

Would this be possible for a text? let's say, the values are:

ID	Name	Value
1	Test1	a
2	Test2	b
3	Test3	c

I would like to get the b, which is the 2nd last value, but it is a text. 

Thanks!

[Ashish_Mathur]

Hi,

Does second last have to be determined based on numbers being in the ID column?

[Ashish_Mathur]

Hi,

Does second last have to be determined based on numbers being in the ID column?

[Anonymous · Accepted solution]

Hi @EvanGetsItDone ,

You can create a measure as below to get it, please find the details in the attachment.

2nd latest value = 
VAR _maxid =
    CALCULATE ( MAX ( 'Table'[ID] ), ALLSELECTED ( 'Table' ) )
VAR _secondmid =
    CALCULATE (
        MAX ( 'Table'[ID] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[ID] < _maxid )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER ( 'Table', 'Table'[ID] = _secondmid )
    )

Best Regards