Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
@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 )
Proud to be a Super User!
@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 )
Proud to be a Super User!
Yep, that is my mistake! This works. Thank you soooo much!
Hi
Does it still work if your dates are not sequential ?
tks R
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |