Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculate 5th date from max date

Hello All,   I am trying to get the last fifth date from the max date in my table.   EmpIDDateDaytype A 21/10/2018 WO A 22/10/2018 AB A 23/10/2018 AB B 24/10/2018 AB A 25...
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • petrovnikitamai's avatar
    petrovnikitamai
    7 years ago
    new column = 
var rankk = RANKX(FILTER('Table';EARLIER('Table'[EmpID])='Table'[EmpID]&&EARLIER('Table'[Daytype])='Table'[Daytype]);'Table'[Date];;DESC)
var last5thDate = IF(rankk=5&&[Daytype]<>"WO";[Date];BLANK())
return last5thDate

    try this

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Greg_Deckler Yes..you got it right...

Any help

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
7 years ago

Anonymous - See if this works for you. Page 3 of the attached file (Table6). 

 

Measure 3 = 
VAR __emplID = MAX('Table6'[EmplID])
VAR __table = FILTER('Table6',[EmplID]=__emplID && [Daytype]="AB")
VAR __table1 = ADDCOLUMNS(__table,"__rank",RANKX(__table,[Date]))
RETURN MAXX(FILTER(__table1,[__rank]=5),[Date])