Forum Discussion
Second Last entry in data set
My data contains submit date, valid entry and RAG status, I want rag status of employees with valid entry yes and date submit is second last.
DAX should filter the valid = Yes, and Employee Code = Employee Code and provide rag status of second max date in submit date.
Below is table, first four column I have in table and I want to add Previous RAG column.
| Date Submit | Emp Code | Valid | RAG | Previous RAG |
| 1-Jan-2019 | 123 | Yes | Red | Green |
| 18-Dec-2018 | 123 | Yes | Green | |
| 1-Jan-2019 | 123 | No | Red | |
| 1-Dec-2020 | 1444 | Yes | Red | Amber |
| 1-Nov-2018 | 1444 | Yes | Amber |
Hi Anonymous
You can create two Calculated columns.
Rank By Date = VAR _rank = RANKX ( FILTER ( 'Table', 'Table'[Valid] = "Yes" && 'Table'[Emp Code] = EARLIER ( 'Table'[Emp Code] ) ), 'Table'[Date Submit], , DESC, DENSE ) RETURN IF ( 'Table'[Valid] = "No", BLANK (), _rank )Previous RAD = VAR val = MAXX ( FILTER ( 'Table', [Rank By Date] = 2 && [Emp Code] = EARLIER ( [Emp Code] ) ), [RAG] ) RETURN IF ( 'Table'[Rank By Date] = 1, val, BLANK () )The result looks like this:
Best Regards
Caiyun Zheng
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Anonymous , Try a new column like
new column =
var _rank = rankx(filter(Table, [Emp] = earlier([Emp ])), [Date Submit],,desc, dense)
Var _status = maxx(filter(Table, [Emp] = earlier([Emp ]) && _rank =2),[status])
return
if(_rank =1,_status, blank())if _rank does not work create a rank column first
rank = rankx(filter(Table, [Emp] = earlier([Emp ])), [Date Submit],,desc, dense)
Hi Anonymous
You can create two Calculated columns.
Rank By Date = VAR _rank = RANKX ( FILTER ( 'Table', 'Table'[Valid] = "Yes" && 'Table'[Emp Code] = EARLIER ( 'Table'[Emp Code] ) ), 'Table'[Date Submit], , DESC, DENSE ) RETURN IF ( 'Table'[Valid] = "No", BLANK (), _rank )Previous RAD = VAR val = MAXX ( FILTER ( 'Table', [Rank By Date] = 2 && [Emp Code] = EARLIER ( [Emp Code] ) ), [RAG] ) RETURN IF ( 'Table'[Rank By Date] = 1, val, BLANK () )The result looks like this:
Best Regards
Caiyun Zheng
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
