Forum Discussion

nthomson's avatar
nthomson
Frequent Visitor
6 years ago
Solved

Value associated with second most recent date

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 f...
  • lbendlin's avatar
    lbendlin
    6 years ago

    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's avatar
nthomson
Frequent Visitor
6 years ago

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's avatar
lbendlin
Icon for Super User rankSuper User
6 years ago

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's avatar
    nthomson
    Frequent Visitor
    6 years ago

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