Forum Discussion
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/10/2018 | AB |
| A | 26/10/2018 | AB |
| A | 27/10/2018 | WO |
| A | 28/10/2018 | WO |
| A | 29/10/2018 | AB |
| A | 30/10/2018 | AB |
| B | 21/10/2018 | WO |
| B | 22/10/2018 | AB |
| B | 23/10/2018 | AB |
| B | 24/10/2018 | AB |
| B | 25/10/2018 | AB |
| B | 26/10/2018 | AB |
| B | 27/10/2018 | WO |
| B | 28/10/2018 | WO |
| B | 29/10/2018 | AB |
| B | 31/10/2018 | AB |
This is the data that iam working on.
And my query looks like this.
Last5thDate = CALCULATE(MAX(Table[Date]),
FILTER(ALLEXCEPT(Table,Table[EmpID]),
NOT(Table[Daytype]="WO" )))-4But it gives me wrong output as 26/10/2018 for A and 27 for B
Can any one please help me out.
MohanV
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 last5thDatetry this
7 Replies
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?
Greg_Deckler Yes..you got it right...
Any helpAnonymous - 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])
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 last5thDatetry this
