Forum Discussion

adkayak86's avatar
adkayak86
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 ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    1 year ago

    You are welcome.  Revise the measure to

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

     

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

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    1 year ago

    then use MAX or MIN