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

4 Replies

  • 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)