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

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'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!