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 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