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

Greg_Deckler's avatar
Greg_Deckler
Community Champion
7 years ago

If I am reading your formula correctly, it looks like you want to find the 5th date from the MAX date that doesn't involve WO's, only AB's, is that correct?

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Greg_Deckler Yes..you got it right...

    Any help

    • Greg_Deckler's avatar
      Greg_Deckler
      Community 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])
      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Greg_Deckler petrovnikitamai thanks for your help.

         

        Both the solutions works perfectly..

         

        But I am looking for the calculated column as i have some calculations needs to be done based on that, afterthat.

         

        thanks.