Value associated with second most recent date

[nthomson]

Hi,

I'm trying to filter the values in a table by the second most recent date. So, from the table below the formula would output '4', because it is associated with 4-Jan.

I am trying to use filter the rows by the appropriate date so it returns the correct value. However when I try to nest the FILTER expression in my calculation, it returns a blank value. I can't include the actual data, but the formula I've got now is: 

2nd Recent Value = CALCULATE(SUM('DATA SHEET ACTUALS'[Value]), FILTER('DATA SHEET ACTUALS', 'DATA SHEET ACTUALS'[Date] = [2nd Recent Date]))

I have created a Measure which returns the value of the second most recent month ('2nd Recent Date') by using EDATE -1 from the most recent date (itself using LASTDATE).

Suggestions much appreciated.

Thanks

Date		Value
01/01/2020		1
02/01/2020		2
03/01/2020		3
04/01/2020		4
05/01/2020		5

[lbendlin]

try this one.  Might need some fine tuning.

2nd Recent Value =

var thisdate= max('DATA SHEET ACTUALS'[Date])

var seconddate = CALCULATE(max('DATA SHEET ACTUALS'[Date]),'DATA SHEET ACTUALS'[Date]<thisdate)

return CALCULATE(SUM('DATA SHEET ACTUALS'[Value]), FILTER(ALL('DATA SHEET ACTUALS'), 'DATA SHEET ACTUALS'[Date] = seconddate))

[nthomson]

Thanks for the help, however I think this leads to similar problem, i.e with the 'seconddate' formula you've suggested, it seems you're not allowed to use a measure within the formula to derive a value, so the '<[thisdate]' portion throws an error.

I've built the measure for the seconddate already, it's just deriving the actual value from it that I'm struggling with.

[lbendlin · Accepted solution]

I just tried the measure on your test data and it works as expected. What error are you getting exactly?

2nd Recent Value = 
var thisdate= max('DATA SHEET ACTUALS'[Date])
var seconddate = CALCULATE(max('DATA SHEET ACTUALS'[Date]),'DATA SHEET ACTUALS'[Date]<thisdate)
return CALCULATE(SUM('DATA SHEET ACTUALS'[Value]),'DATA SHEET ACTUALS'[Date] = seconddate)

[nthomson]

Apologies, user error when entering - working now. Many thanks for your help!