Hopefully this makes sense:
What I am trying to do get is get the latest non-blank column information by student when dealing with a file that has multiple years. keep in mind that there are 5 academic years of information in my file but the below is based on a single student. The parameter that repeats for each academic year is their student ID.
I have a column that is generated from the following so that it gives me a “Military Status Column”
First the Prior Military Branch Column is tuned into two groups, If there is a branch of service then it is grouped as “YES” if this column is blank or contains anything else like military dependent then its group as “NO”.
So then I create the following series of formulas:
AY End Civilian = IF('WW Registrations'[Academic Year End Military or Civilian]= "Civilian" && ('WW Registrations'[AY END - Prior Branch of Service Status]="No" || 'WW Registrations'[Acad Year End Prior Military Civilian Type Description]=""),"Civilian")
AY End Military = IF('WW Registrations'[Academic Year End Military or Civilian]="Military", "Military")
AY End Veteran = IF('WW Registrations'[Academic Year End Military or Civilian]= "Civilian" && ('WW Registrations'[AY END - Prior Branch of Service Status]="Yes"),"Veteran")
Doing this allows me to create a column:
AY End Status = ('WW Registrations'[AY End Civilian] & ('WW Registrations'[AY End Veteran] & 'WW Registrations'[AY End Military]))
So back to the example that I added above, the individual would be classified as “Veteran” in the AY End Status Column thats created from the above formula. What I notice is happening is when using this to create visuals its only giving the very first occurrence of the status so he shows as “Civilian” because for 2012-13 both were the Military/Civilian Status and the Prior Military Branch Status fields were blank. But notice in 2014-15 he was Civilian and had a prior branch of Marines which means he should be considered Veteran.
How do I make it so that the column that is created called “AY End Status” grabs the most current non-blank status for the individual? Once they are veteran they are always veteran and we would not classify them as Civilian. Or when creating a visual that includes multiple academic years how do we tell it to grab the most current non-blank status. Or is there a way to establish a hierarchy by status? Meaning if the person is veteran then Civilian can never override/replace it.
Why would those fields be blank? Its a data entry problem caused by having multiple staff entering and validating information. If the student doesnt submit documentation or use their military or veteran funding to pay for classes then no one goes back and update the information by academic year.
I hope I am making sense.