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
Icon for Community Champion rankCommunity 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
    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])
    • 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.

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Greg_Deckler petrovnikitamai

        What if my data model is looks like this and whenever i get the "NT" value then my rank count should get start from 1 again.

         

        EmpIDDateDaytype

        A21-10-2018WO
        A22-10-2018AB
        A23-10-2018AB
        A24-10-2018AB
        A25-10-2018NT
        A26-10-2018AB
        A27-10-2018WO
        A28-10-2018WO
        A29-10-2018AB
        A30-10-2018AB
        B21-10-2018WO
        B22-10-2018AB
        B23-10-2018NT
        B24-10-2018AB
        B25-10-2018AB
        B26-10-2018NT
        B27-10-2018WO
        B28-10-2018WO
        B29-10-2018AB
        B31-10-2018AB

         

        Here For B, On 26/10/2018 it is NT, so when ever i get NT in middle of my DayTpe column then it should count from 1 again.

         

        Any Suggestions