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

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