Forum Discussion
Solved
Identifying latest status within a given year?
Hi there, I have a calculated column with the following status fields: 1. Surveyed, 2. Interviewed, 3. Scheduled, 4. Eligible, and 5. Removed. I'm having an issue where there are several dif...
You can create a new table to get the last survey date for each client in the each year.
Table = SUMMARIZE(Table1,Table1[Client Code],Table1[Survey status year],"LastDate",CALCULATE(MAX(Table1[Survey Month]),ALLEXCEPT(Table1,Table1[Client Code],Table1[Survey status year])))
Then create a calculate column to get the status by using LOOKUPVALUE function.
status = LOOKUPVALUE(Table1[Status for this year?],Table1[Client Code],'Table'[Client Code],Table1[Survey Month],'Table'[LastDate])
Regards,
Charlie Liao
Gotcha! Below is a revised sample. Is there a way to provide only the latest date within the given year? i.e. if status for this year is blank or if survey status year = 2017?
| Client Code | Survey Month | Wave Number | Survey status year | Status for this year? |
| Client A | 1/1/2017 | 7 | 2017 | 5. Removed |
| Client B | 11/1/2016 | 5 | 2016 | |
| Client C | 5/1/2017 | 11 | 2017 | 1. Surveyed |
| Client D | 6/1/2017 | 12 | 2017 | 2. Eligible |
| Client A | 7/1/2017 | 13 | 2017 | 1. Surveyed |
| Client C | 8/1/2017 | 14 | 2017 | 2. Eligible |
You can create a new table to get the last survey date for each client in the each year.
Table = SUMMARIZE(Table1,Table1[Client Code],Table1[Survey status year],"LastDate",CALCULATE(MAX(Table1[Survey Month]),ALLEXCEPT(Table1,Table1[Client Code],Table1[Survey status year])))
Then create a calculate column to get the status by using LOOKUPVALUE function.
status = LOOKUPVALUE(Table1[Status for this year?],Table1[Client Code],'Table'[Client Code],Table1[Survey Month],'Table'[LastDate])
Regards,
Charlie Liao