Forum Discussion
Return Previous Value
Hi all,
I've been stuck on this for awhile and have browsed online, but I can't seem to figure it out! I have a table of unique IDs, dates, and statuses. I want to return the previous status for each ID. Any help is much appreciated! I put some sample data in to show what I want to accomplish.
| ID | Date | Status | Previous Status |
| 1 | 1/1/2019 | Good | |
| 1 | 1/2/2019 | Medium | Good |
| 1 | 1/3/2019 | Bad | Medium |
| 2 | 1/1/2019 | Bad | |
| 2 | 1/2/2019 | Medium | Bad |
| 2 | 1/3/2019 | Medium | Medium |
| 3 | 1/1/2019 | Great | |
| 3 | 1/2/2019 | Medium | Great |
| 4 | 1/1/2019 | Good | Medium |
| 4 | 1/2/2019 | Great | Good |
| 4 | 1/3/2019 | Great | Great |
| 4 | 1/4/2019 | Medium | Great |
Anonymous -
is 'Medium' an error? Does it not belong to [ID] = 3?
Anonymous wrote:ID Date Status Previous Status 1 1/1/2019 Good 1 1/2/2019 Medium Good 1 1/3/2019 Bad Medium 2 1/1/2019 Bad 2 1/2/2019 Medium Bad 2 1/3/2019 Medium Medium 3 1/1/2019 Great 3 1/2/2019 Medium Great 4 1/1/2019 Good Medium 4 1/2/2019 Great Good 4 1/3/2019 Great Great 4 1/4/2019 Medium Great This mostly works except for what I noted above:
Column = LOOKUPVALUE( 'Table'[Status], 'Table'[ID],'Table'[ID], 'Table'[Date],'Table'[Date]-1 )
3 Replies
Anonymous -
is 'Medium' an error? Does it not belong to [ID] = 3?
Anonymous wrote:ID Date Status Previous Status 1 1/1/2019 Good 1 1/2/2019 Medium Good 1 1/3/2019 Bad Medium 2 1/1/2019 Bad 2 1/2/2019 Medium Bad 2 1/3/2019 Medium Medium 3 1/1/2019 Great 3 1/2/2019 Medium Great 4 1/1/2019 Good Medium 4 1/2/2019 Great Good 4 1/3/2019 Great Great 4 1/4/2019 Medium Great This mostly works except for what I noted above:
Column = LOOKUPVALUE( 'Table'[Status], 'Table'[ID],'Table'[ID], 'Table'[Date],'Table'[Date]-1 )Hi
Does it still work if your dates are not sequential ?tks R
