Forum Discussion

Frequent Visitor

1 year ago

Solved

2nd to most recent date

Hi all, I have two tables in my dataset: HSCompanies: Contains 1 row for each company HSHealthScores: Contains multiple rows of health scores for each company I'm trying to retrieve the ...

1 year ago

You are welcome. Revise the measure to

PenultimateScore = CALCULATE(MAX(HSHealthScores[Score]),DATESBETWEEN('Calendar'[Date],[PenultimateScoreDate],[PenultimateScoreDate]))

Icon for Super User rank

Super User

1 year ago

Hi,

PBI file attached.

PBI_Example.pbix65 KB

adkayak86
Frequent Visitor
1 year ago
Thank you! One last question if I may... how can I return the penultimate score?

I used this in the attached example, which works, but it doesn't work in my actual dataset because there are duplicate date values (I get a message that says a table with multiple values was supplied when it expected single values). I also tried to add a CompanyID filter to this, but it wasn't working.

PenultimateScore = LOOKUPVALUE('HSHealthScores'[Score], 'HSHealthScores'[StartDate], [PenultimateScoreDate])

PBI_Example.pbix
- lbendlin
  Super User
  1 year ago
  
  PenUltimateScore = Calculate(sum('HSHealthScores'[Score]),(OFFSET(-1,ALLSELECTED('HSHealthScores'[StartDate])))
  
  Please do not use LOOKUPVALUE. It has a very different purpose.
  - adkayak86
    Frequent Visitor
    1 year ago
    Hi lbendlin ,
    
    Sorry for not specifying the right data format in my model. The example is using a # score, but my actual dataset is using A+, A, B+, B, etc, so I cannot use SUM.
    
    Appreciate the assistance - it's very helpful while learning.
- Ashish_Mathur
  Super User
  1 year ago
  You are welcome. Revise the measure to
  
  PenultimateScore = CALCULATE(MAX(HSHealthScores[Score]),DATESBETWEEN('Calendar'[Date],[PenultimateScoreDate],[PenultimateScoreDate]))