Forum Discussion
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 most recent health score, and the one right before that.
I can retrieve the last score date by using this formula:
MostRecentCRFDate =
CALCULATE (
MAX ( 'HSHealthScores'[StartDate] ),
ALLEXCEPT ( 'HSCompanies', 'HSCompanies'[CompanyID] )
)
But I am having trouble with the 2nd most recent date. I found similar question and tried this calculated column, but it doesn't seem to be working:
SecondMostRecentCRFDate =
CALCULATE (
MAX ( 'HSHealthScores'[StartDate] ),
ALLEXCEPT ( 'HSCompanies', 'HSCompanies'[CompanyID] ),
'HSHealthScores'[StartDate]
< EARLIER ( 'HSCompanies'[MostRecentCRFDate] )
)
Appreciate any help!
You are welcome. Revise the measure to
PenultimateScore = CALCULATE(MAX(HSHealthScores[Score]),DATESBETWEEN('Calendar'[Date],[PenultimateScoreDate],[PenultimateScoreDate]))
12 Replies
- Ashish_Mathur
Super User
Hi,
PBI file attached.
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])
- lbendlin
PenUltimateScore = Calculate(sum('HSHealthScores'[Score]),(OFFSET(-1,ALLSELECTED('HSHealthScores'[StartDate])))Please do not use LOOKUPVALUE. It has a very different purpose.
- lbendlin
Identifying the penultimate date has become much, much easier now thanks to the WINDOW functions.
Use
OFFSET(-1,ALLSELECTED('HSHealthScores'[StartDate]))to grab that.
Is that the whole formula? I get this message:
A table of multiple values was supplied where a single value was expected.
- lbendlin
SecondMostRecentCRFDate = MAXX(OFFSET(-1,ALLSELECTED('HSHealthScores'[StartDate])),[StartDate])
Hello adkayak86 ,
Thank you for reaching out to the Microsoft Fabric Community.
May I kindly ask if the issue has been resolved after reviewing the guidance provided by Ashish_Mathur and lbendlin ? If so, please consider marking the most helpful response as the accepted solution. This will assist other community members facing similar challenges in finding a quicker resolution.
Thank you.
Hi adkayak86 ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hi adkayak86 ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
