Getting the most current status

[jmwdba]

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.

[sam.mckay]

Just reviewing this and have none read through it multiple times.

I’m finding it very difficult to understand everything here. It’s always hard to imagine all the variables of a model, multiple formulas, then also the context of the calc and trying to come to the right conclusion.

Is it possible to really break this down into one specific formula that you need assistance with, or even one part of a formulas behaviour.

I understand you’ve given quite detailed description here but it honestly makes it more confusing because it brings in too many variables and makes it harder to give a solution.

The best thing to do is break down one very specific thing and then work through that.

The only thing that matters with any formula is first the ‘initial context’ - you must think deeply about what this is, and then second what the formula is then doing inside the initial context.

The initial context will come from your data model, so have a think of exactly which filters are in place for each specific result. Once you understand this well, then you can move onto what the formula is doing in the revised context it has been given.

Chrs