Identifying latest status within a given year?

[Dakotars]

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 different statuses within a year, for example Client A was removed in January but was then Surveyed in October. When I try to determine participation rates, I'm getting two statuses for Client A, when I only care about what the latest status is (the October one).

Anyone have any ideas for creating a calculated column that looks at the latest status within a year?

Thanks!

Dakota

[Dakotars]

Below is a sample of what I'm looking at. As you can see, there are two statuses for Client A, I'd like to return the surveyed value. I'm thinking if I keep the numbers in front I can utilize the MIN function. Is there a way to evaluate MIN for a specific Client value?

Client	Survey Year	Status for this year?
Client A	2017	5. Removed
Client B	2016
Client C	2017	1. Surveyed
Client D	2017	2. Eligible
Client A	2017	1. Surveyed
Client C	2017	2. Eligible

[Interkoubess]

Hi @Dakotars,

Do you have the whole date ( for example Year Month Day) or you simply have Survey Year?

Ninter

[Dakotars]

I have the whole date available. However, I only want to look at this based on the current year, which is why I created the 'Status for this year' field

[Interkoubess]

But for example I will based my formula on this field ( whole year) to grab for example the latest date.

If you can give another sample with the date I can give it a try.

Ninter

[Dakotars]

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

[v-caliao-msft · Accepted solution]

@Dakotars,

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